Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

paulyeo11
Not applicable

Modify SET expression from YTD and Month Close.

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

1 Solution

Accepted Solutions
tresesco
Not applicable

Re: Modify SET expression from YTD and Month Close.

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

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

5 Replies
swuehl
Not applicable

Re: Modify SET expression from YTD and Month Close.

Maybe just

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

sunny_talwar
Not applicable

Re: Modify SET expression from YTD and Month Close.

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

Capture.PNG

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?

tresesco
Not applicable

Re: Modify SET expression from YTD and Month Close.

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

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

paulyeo11
Not applicable

Re: Modify SET expression from YTD and Month Close.

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

sunny_talwar
Not applicable

Re: Modify SET expression from YTD and Month Close.

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