Set Analysis for Max of Date when date is a string

[tomdabek]

I have a field billling_date that is displayed in the format '2020-10-7' for October 7th.

When i evaluate max(billing_date) i get a numerical value 44111

sum({<billing_date={$(=max(billing_date))}>}_volume) 

does not work, just returns zero.  However if i use

sum({$<billing_date={'2020-10-07'}>} _volume)

I get the results I want.  
What do i need to do in order to create a set analysis expression that will work correctly?

[ramchalla]

@tomdabek 

if billing_date is in string format, convert it to date by using Date#(billing_date,'YYYY-MMM-DD') and you can use the below expression.

sum({<billing_date={$(=Date(max(billing_date),'YYYY-MM-DD')}>}_volume)

[tomdabek]

This doesn't work. 

I can correctly display Date(max(billing_date),'YYYY-MM-DD')) as an expression in a column it correctly shows the value as a date - but within the set analysis expression it doesn't work

[marcus_sommer · Accepted solution]

The suggestion from Ramchalla is missing single-quotes around the $-sign expansion and it missed an ending bracket - therefore try it in this way:

sum({<billing_date={'$(=Date(max(billing_date),'YYYY-MM-DD'))'}>}_volume)

- Marcus

[MayilVahanan]

Hi Tom,

Please follow the Marcus suggestion. Additionally, if you are using the value in multiple places, pls create the variable for Max Date & use that variable in set analysis or required places. 

Like 
vMaxBillingDate  =Date(max(billing_date),'YYYY-MM-DD')

Exp: sum({<billing_date={'$(vMaxBillingDate)'}>}_volume)

So that, if any changes required, you can change in variable alone. Time saving & less maintenance. 

Thanks & Regards,

Mayil Vahanan R

[tomdabek]

Thank you to both for your help with this.