topic YTD/MTD set analysis calculation required in QlikView

YTD/MTD set analysis calculation required

rajtechnocraft — Thu, 12 Jan 2012 04:25:59 GMT

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

Re: YTD/MTD set analysis calculation required

SunilChauhan — Thu, 12 Jan 2012 06:16:50 GMT

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

YTD/MTD set analysis calculation required

Thu, 12 Jan 2012 06:46:08 GMT

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

YTD/MTD set analysis calculation required

Thu, 12 Jan 2012 08:05:09 GMT

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