topic Re: Modify SET expression from YTD and Month Close. in QlikView

Modify SET expression from YTD and Month Close.

paulyeo11 — Tue, 29 Mar 2016 08:24:20 GMT

Hi All

I have below expression , it will return YTD sales :-

Sum({$<year = {$(=Max(year)-1)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/$(Columndim89)/1000)

Now i need to modify the above expression to display month closed. Meaning only display Jan and Feb sales amount exclude march sales amount.

So the result should only shown 18 + 89

Paul

Re: Modify SET expression from YTD and Month Close.

swuehl — Tue, 29 Mar 2016 08:29:08 GMT

Maybe just

month = {"<$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}

Re: Modify SET expression from YTD and Month Close.

sunny_talwar — Tue, 29 Mar 2016 08:29:54 GMT

You mean 18 (Jan) + 122 (Feb)?

How do you determine if something is a closed month or not? Is there a flag or is it based on today's date and month?

changing <= to < might work, but this will impact selection in other years. For example, if you select 2015 then you might only see 11 months, instead of 12. Is that what you want to see when you select 2015?

Re: Modify SET expression from YTD and Month Close.

tresB — Tue, 29 Mar 2016 08:30:05 GMT

Just replace the '<=' by '<' like:

Sum({$<year = {$(=Max(year)-0)}, month = {"<$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/$(Columndim89)/1000)

Re: Modify SET expression from YTD and Month Close.

paulyeo11 — Tue, 29 Mar 2016 08:39:49 GMT

Hi Sunny

I just check and confirm that it will impact when i select 2015. it will display only jan till nov sales.

it is okay. as i only use this expression in QS KPI sheet , where my GL table alway update once a month , so i need to use month closed.

Paul

Re: Modify SET expression from YTD and Month Close.

sunny_talwar — Tue, 29 Mar 2016 08:42:35 GMT

Sounds good. If you are able to get what you want, then it is all good