11 Replies Latest reply: Aug 26, 2016 9:31 AM by Ishan Bansal

# YTD In qlik sense

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.

• ###### Re: YTD In qlik sense

Hi

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

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)

• ###### Re: YTD In qlik sense

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?

• ###### Re: YTD In qlik sense

Hi

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

try this.

Regards,

• ###### Re: YTD In qlik sense

Thanks Marco.

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

• ###### Re: YTD In qlik sense

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)

• ###### Re: YTD In qlik sense

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)

• ###### Re: YTD In qlik sense

Thank you very much Jonathan. Its working.

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

• ###### Re: YTD In qlik sense

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

• ###### Re: YTD In qlik sense

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.

• ###### Re: YTD In qlik sense

This is not too difficult if your calender has a week sequence number, because week 52/3 of the previous year would have a sequence number 1 less than week 1 of the current year. Without a sequence number, it gets more difficult. Both of these are rather dependent on how you handle the partial week at the start and end of the year.

I suggest that you start a new thread to ask this question and for assistance in adding a week sequence number. You can always add a link back to this original question if needed.

• ###### Re: YTD In qlik sense

I will do that. Thanks Jonathan