YTD/MTD set analysis calculation required

[rajtechnocraft]

Hi Community, I just wanted MTD and YTD calculation for sales value and trade date using set analysis Can anyone from experts help me out. Thanks in  advance Regards Raj

[SunilChauhan]

YTD:

sum({$<Year = {$(=max(Year ))}, Month= {"<=$(=max({<Year ={$(=max(Year ))}>} Month))"}>} distinct sales)

MTD:

sum({$<Year = {$(=max(Year ))}, Month= {$(=max({<Year ={$(=max(Year ))}>} Month)},Date= {"<=$(=max({<Year ={$(=max(Year ))}>} Date)"}>} distinct sales)

remember month should be in numeric and date in date format or number format

hope this help

[#3]

Hi Sunil,

I think you just put it otherwise.

For MTD it should be:

sum({$<Year = {$(=max(Year ))}, Month= {$(=max({<Year ={$(=max(Year ))}>} Month)},Date= {"<=$(=max({<Year ={$(=max(Year ))}>} Month)"}>} sales)

and For YTD it should be :

sum({$<Year = {$(=max(Year ))}, Month= {"<=$(=max({<Year ={$(=max(Year ))}>} Month))"}>} sales)

Regards,

Ashutosh

[#4]

Hi Raj,

While the above expressions will work but just to make it a little simpller, You can also try this also

YTD:

=sum({$<Year={$(=Max(Year))},Month={"<=$(=max(Month))"}>}Sales)

MTD:

=sum({$<Year={$(=Max(Year))},Month={$(=max(Month))},Date={"<=$(=max(Date))"}>}Sales)

if you want to disregard selections in all other fields then just use 1 in place of $, so the same will be as

YTD:

=sum({1<Year={$(=Max(Year))},Month={"<=$(=max(Month))"}>}Sales)

MTD:

=sum({1<Year={$(=Max(Year))},Month={$(=max(Month))},Date={"<=$(=max(Date))"}>}Sales)

Hope this helps

Regards,

Ashutosh