Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paulyeo11
Master
Master

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
MVP
MVP

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

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

View solution in original post

5 Replies
swuehl
MVP
MVP

Maybe just

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

sunny_talwar

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
MVP
MVP

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

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

paulyeo11
Master
Master
Author

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

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