topic SET expression current month ok previous not ok in QlikView

SET expression current month ok previous not ok

paulyeo11 — Thu, 02 Jun 2016 19:43:27 GMT

Hi All

I have below expression working fine for retuen current selected month amount :-

(

sum({$

year = {$(=max(year))},

Month = {"<=$(=max({<year={$(=max(year))}>} Month))>$(=max({<year={$(=max(year))}>} Month)-1)"}

} Amount/1000)

)

I try to modify the above expression to below , in order to display previous month sales :-

(

sum({$

year = {$(=max(year))},

Month = {"<=$(=max({<year={$(=max(year))}>} Month))>$(=max({<year={$(=max(year))}>} Month)-2)"}

} Amount/1000)

)

But it return null value. Can some one advise me where go wrong ?

Paul

Re: SET expression current month ok previous not ok

paulyeo11 — Thu, 02 Jun 2016 19:44:14 GMT

my QV Doc

Re: SET expression current month ok previous not ok

alexdataiq — Thu, 02 Jun 2016 21:52:28 GMT

You could simplify those expressions by just using:

Current Month:

Sum({$< year = {$(=max(year))}, month = {$(=Max(month))}>} Amount/1000)

Past Month:

Sum({$< year = {$(=max(year))}, month = {$(=Max(month)-1)}>} Amount/1000)

But I would suggest create a variable for your previous Month to take into account January, something like:

vPreviousMonth

= If(Max(month) - 1 = 0, 12, month)

And use it in your expression:

Sum({$< year = {$(=max(year))}, month = {$(vPreviousMonth)}>} Amount/1000)

Cheers

Re: SET expression current month ok previous not ok

paulyeo11 — Fri, 03 Jun 2016 02:32:16 GMT

Hi Alejandro

Thank you very much for your advise. I notice that after you simplify the expression , now user must select current month and year. In fact i have make a lot of change to avoid select select month and year. So the same expression i can use in QS.

Paul