YTD In qlik sense

[ishanbansal1204]

i am working on YTD calculation and i have week and year as a parameter.

Sum({1 <WEEK={">}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"}, Year = {$(=MAX(Year))}>}M_COUNT_WEEK)

when i am using the above query its always fetching me the max of year. ideally it should give me the data for the year that i am selecting. i have 2 years 2016 and 2015 when i am selecting 2015 it still shows me 2016 data. please help.

[Anonymous]

Hi

First of all  , I do not understand the  selection you made . Seems to be some error

1)Please check highlighted part .

Sum({1 <WEEK={">}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"},

Year = {$(=MAX(Year))}>}  M_COUNT_WEEK)

2)

you have declared   Year = {$(=MAX(Year))  ,  AND  '1' in the expression due to this i think year always slects MAX year i.e 2016 !!?

Sum({1 <WEEK={">}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"},

Year = {$(=MAX(Year))}>}  M_COUNT_WEEK)

[marcohadiyanto]

Hi

Sum({<WEEK={">=$(=Max({<Year={$(=MAX(Year)-1)}>}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"}, Year = {$(=MAX(Year))}>}M_COUNT_WEEK)

try this.

Regards,

[ishanbansal1204]

Hi Alluraiah,

the query is Sum({1<WEEK={"<=$(=Max({<Year={$(=Max(Year))}>}WEEK))"},Year = {$(=Max(Year))}>}M_COUNT_WEEK)

Can you let me know what might me the correct syntax for year?

[ishanbansal1204]

Thanks Marco.

But this is giving me exact value for that week. but i need ytd value.

[ishanbansal1204]

Hi Marco

If i have 3 years then will this condition work?

Sum({<WEEK={">=$(=Max({<Year={$(=MAX(Year)-1)}>}WEEK))<=$(=Max({<Year={$(=MAX(Year))}>}WEEK))"}, Year = {$(=MAX(Year))}>}M_COUNT_WEEK)

[jonathandienst · Accepted solution]

This expression should work:

Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year))}>} M_COUNT_WEEK)

If that is not working, you might want to post a small sample qvf file for more detailed understanding of your model and more accurate help.

And yes, this should work no matter how many years you have. If you want to compare the latest (or selected) year with the previous year:

Current/selected year

Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year))}>} M_COUNT_WEEK)

Prior year (to same week number as current/selected year):

Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year)-1)}>} M_COUNT_WEEK)

[ishanbansal1204]

Thank you very much Jonathan. Its working.

Thanks to others as well for helping me out in this problem.

[oknotsen]

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

[ishanbansal1204]

Hi Jonathan,

I want to compare week on week value

that is current week - previous week / previous week

how can we achieve that?

ideally it should also cover if current week is 1 so the previous week will be max of week of the previous year.