topic Prev YTD in QlikView

Prev YTD

dmac1971 — Thu, 27 Mar 2014 13:15:25 GMT

This expression is "nearly" working, expect that it only gives me last Feb and not Jan & Feb?

=SUM({<Year={'$(vPriorYear)'},Month={'$(vMaxMonth)'}>} Sales)

Any idea what I need to change?

Thanks.

Re: Prev YTD

Thu, 27 Mar 2014 13:20:51 GMT

Writing Month={'$(vMaxMonth)'} means it will show the last month, you might want to change that

Cheers,

Lucas

Re: Prev YTD

lironbaram — Thu, 27 Mar 2014 13:22:51 GMT

try this

SUM({<Year={'$(vPriorYear)'},Month={'<=$(vMaxMonth)'}>} Sales)

Re: Re: Prev YTD

dmac1971 — Thu, 27 Mar 2014 13:41:14 GMT

For some reason changing to

SUM({<Year={'$(vPriorYear)'},Month={'<=$(vMaxMonth)'}>} Sales)

Gives me 0, I had tried this before. Maybe its how I am setting up the variable?

vMaxMonth = Month(max([Transaction Date]))

Re: Prev YTD

Thu, 27 Mar 2014 13:47:40 GMT

Setting vMaxMonth to Month(max([Transaction Date])) means you will get 1 month, try making another variable v2ndLastMonth = Month(max([Transaction Date])-1) and then adding it into your set analysis. This will give you the last month and the second-last month

Re: Prev YTD

DavidFoster1 — Thu, 27 Mar 2014 14:20:17 GMT

It is either (have not had chance to test....)

1) the <= quote are in the wrong place:

SUM({<Year={'$(vPriorYear)'},Month={<='$(vMaxMonth)'}>} Sales)

2) You need to use double quotes to get the expression to evaluate in the brackets

SUM({<Year={"$(vPriorYear)"},Month={"<=$(vMaxMonth)"}>} Sales)