Set Analysis latest date or selected date

[PrashantRupani]

Hi,

I have a need to write a set analysis expression for count of open orders as of the latest month if no Month filter is chosen.

If say two months are chosen like Dec 2020 and Jan 2021 then show count of open orders as of Jan 2021

If three months - Dec 2020, Jan 2021, Feb 2021 then count of open orders as of Feb 2021. 

How do I achieve this?

Thanks

[Vegar · Accepted solution]

Try something like this. 

Count ({$<[Month] ={'$(=maxstring([Month]))'} >} [Order ID])

(I am assuming that your [Month] is a dual value containing both a numeric and string representation.) 

[MikeA]

If you're wanting to allow multiple selections to still sum you may need to modify Vegar's suggestion along the lines of  -

Count ({$<[Month]={'$(=if(GetSelectedCount([Month])<=1,maxstring([Month]),concat(distinct [Month],''',''')))'}>} [Order ID])

Although for better performance it might be better to have the concat(distinct [Month],''',''') in a variable starting with an =, it then handles the if and concat distinct and removes these from the expression so isn't calculated on every call.

vMyVar-  =chr(39) & if(getselectedcount([Month])<=1,maxstring([Month]),concat(distinct [Month],''',''')) & chr(39)

Expression- Count ({$<[Month]={$(vMyVar)}>} [Order ID])

[PrashantRupani]

Thanks this worked and your assumption was correct. 

[PrashantRupani]

Hi Mike,

Didnt need the sum but your solution helped me to learn if a sum was required ever in the future. Thanks for this solution.

One question - I understand the need of a concat and the separator is a comma (,) but what do those 3 inverted quotes(surrounding the comma) indicate -  ''','''? 

Thanks

[PrashantRupani]

Why use chr(39) ?

Can you please show if multiple months are selected then how would the variable evaluate?

[MikeA]

the 3 apostrophes result in a single apostrophe in the end, so concat is putting apostrophe comma apostrophe between each of the values and the string returned will be 29 - Jan','28 - Jan','27 - Jan

The Chr(39) is just another way of doing apostrophes and will put the two on the ends of the string resulting in '29 - Jan','28 - Jan','27 - Jan' which can then be passed to the set analysis

I wasn't sure if you had text or numbers so I added them in just in case.

[PrashantRupani]

Great. Thanks for the detailed explanation Mike. I learned something new today.

I appreciate you taking out time to explain me. Thanks again.