topic Re: YTD In Qlikview in QlikView

YTD In Qlikview

rahulsingh12 — Mon, 06 Nov 2017 10:38:28 GMT

Hi,

I have two fields year and month as text.

I want to calculate YTD for current selected Month by the user.

Thus if the user selects jan it should give sum of sales for Jan only if they select Feb they should get for Jan and feb and hence forth.

I wrote the following code:

sum({<MonthYear={">=$(=vMinYearMonth)<=$(=vMaxYearMonth)"}>}[Volume])

I created a field YearMonth by converting Month into numbers and concatenating with Year.

Thus vminyearmonth is min YearMonth for any selected Month,similary vMaxYearMonth is max of YearMonth for any selected yearmonth(In this case the selected month).

But the sum I get is only for the selected month not YTD.

what is it that I am doing wrong.

Regards.

Re: YTD In Qlikview

Anonymous — Mon, 06 Nov 2017 10:43:33 GMT

Hello Rahul,

Try This,

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

Re: YTD In Qlikview

Anonymous — Mon, 06 Nov 2017 10:49:41 GMT

You should take a look in this post:

YTQ, QTD, MTD and WTD

Re: YTD In Qlikview

prat1507 — Mon, 06 Nov 2017 10:49:44 GMT

Since you want YTD values, for ex. if you select Feb, you must have the sum for Jan,Feb. Since this is the intent, your vMinYearMonth should not depend on the selected month and must be fixed.

Since you haven't fixed it, when you select Feb, it selects both vMinYearMonth and vMaxYearMonth as Feb for the selected year and similarly for any other selected month.

Just add Month= in your vMinYearMonth set analysis expression.

I hope this would work.

Regards
Pratyush

Re: YTD In Qlikview

rahulsingh12 — Mon, 06 Nov 2017 10:51:13 GMT

Hi Pratyush,

I have tried that.

But it doesnt seem to work.

Regards,

Rahul

Re: YTD In Qlikview

Anil_Babu_Samineni — Mon, 06 Nov 2017 10:51:37 GMT

We need variable definition too, Because vMinYearMonth need to be calculated like YearStart(Min(Year))

Re: YTD In Qlikview

rahulsingh12 — Mon, 06 Nov 2017 12:33:32 GMT

It is working.

can you tell me how will I last year YTD for same selected month and year?

Regards,

Rahul

Re: YTD In Qlikview

Anil_Babu_Samineni — Mon, 06 Nov 2017 13:12:25 GMT

Like this?

YearStart(AddYears(Today(),-1))

Month(AddYears(Today(),-1))