5 Replies Latest reply: Mar 29, 2016 4:42 AM by Sunny Talwar

# 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

• ###### Re: Modify SET expression from YTD and Month Close.

Maybe just

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

• ###### Re: Modify SET expression from YTD and Month Close.

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.

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.

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

• ###### 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)