Modify SET expression from YTD and Month Close.

[paulyeo11]

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

[swuehl]

Maybe just

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

[sunny_talwar]

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?

[tresesco · Accepted solution]

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

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

[paulyeo11]

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