topic Re: Set Analysis for Max of Date when date is a string in QlikView

Set Analysis for Max of Date when date is a string

tomdabek — Thu, 08 Oct 2020 16:42:43 GMT

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?

Re: Set Analysis for Max of Date when date is a string

ramchalla — Thu, 08 Oct 2020 17:03:37 GMT

@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)

Re: Set Analysis for Max of Date when date is a string

tomdabek — Thu, 08 Oct 2020 17:50:59 GMT

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

Re: Set Analysis for Max of Date when date is a string

marcus_sommer — Fri, 09 Oct 2020 07:01:58 GMT

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

Re: Set Analysis for Max of Date when date is a string

MayilVahanan — Fri, 09 Oct 2020 08:02:45 GMT

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

Re: Set Analysis for Max of Date when date is a string

tomdabek — Fri, 09 Oct 2020 13:20:27 GMT

Thank you to both for your help with this.