topic Re: How to find the MaxDate for a Calculated Expression in QlikView

How to find the MaxDate for a Calculated Expression

raajeshn — Fri, 22 Mar 2013 11:42:52 GMT

Hi QlikViewers,

Requesting your help here. I have a Straight Table in QlikView Application. In addition to this chart, I have multiple filters. Chart will be responding to the selections made in these filters. Each time the selection is made, the max month may change depending upon the filters.

Now, will it be possible for us to get the Max Month from the above chart (in the first case, it will be Mar-13 and 91 - and in second case it will be Dec12)? Essentially, I would want to display the max month, min month and total number of months shown in the chart in a separate text objects.

Appreciate your support.

Thanks & Regards,

Raajesh N

Re: How to find the MaxDate for a Calculated Expression

sushil353 — Fri, 22 Mar 2013 11:47:04 GMT

have you tried to use

Max(EventMonth)

Min(Eventmonth)

Re: How to find the MaxDate for a Calculated Expression

raajeshn — Fri, 22 Mar 2013 11:52:20 GMT

Hi Sushil,

Thanks for the quick response.

However, that will not work - because, I am using a set expression in Expression used for No. of Events. Hence, I would that information to be derived from Chart.

Thanks & Regards,

Raajesh N

Re: How to find the MaxDate for a Calculated Expression

Gysbert_Wassenaar — Fri, 22 Mar 2013 11:54:12 GMT

Something like this:

=FirstSortedValue(EventMonth, -[No. of Events])

or if No. of Events is an expression that counts events:

=FirstSortedValue(EventMonth, -aggr(max(aggr(count([No. of Events]),EventMonth)),EventMonth))

Re: How to find the MaxDate for a Calculated Expression

sushil353 — Fri, 22 Mar 2013 11:54:46 GMT

Please post your sample application

Re: How to find the MaxDate for a Calculated Expression

Fri, 22 Mar 2013 11:56:04 GMT

try this

max(date(date#(EventMonth,'MMM-YY'),MMM))

Re: How to find the MaxDate for a Calculated Expression

raajeshn — Fri, 22 Mar 2013 12:25:00 GMT

Hi Gysbert,

Thanks for the response. However, this gives me an unexpected answer.

I will create a mock by tomorow and upload it by tomorrow.

Thanks & Regards,

Raajesh N

Re: How to find the MaxDate for a Calculated Expression

raajeshn — Fri, 22 Mar 2013 12:25:36 GMT

Sushil - I will create a mock and update it by tomorrow.

Thanks & Regards,

Raajesh N

Re: How to find the MaxDate for a Calculated Expression

raajeshn — Fri, 22 Mar 2013 12:26:33 GMT

Viswa - I am not trying to change the format. Basically, I need the total number of Rows, Min(Month Year) and Max(Month Year) from the chart.

Thanks & Regards,

Raajesh N

Re: How to find the MaxDate for a Calculated Expression

Fri, 22 Mar 2013 13:24:14 GMT

try this in a separate text-box:

=MIN(EventMonth) & ',' & MAX(EventMonth) & ',' & count(distinct EventMonth) & ' months'

Re: How to find the MaxDate for a Calculated Expression

raajeshn — Mon, 25 Mar 2013 11:36:52 GMT

Hi All,

Thansk for all the responses. However, sorry - I am yet to get an answer. I guess I need to give more information on the issue and hence, I have created a mock for the same. I have given a snapshot of the same along with data being used + the expected result in two text boxes - given just below the charts.

Also, the text boxes (currently hardcoded should change as per the filters - I have added only one filter now, but there will be multiple filters in the actual application).

Appreciate your support.

Thanks & Regards,

Raajesh N

Re: How to find the MaxDate for a Calculated Expression

Gabriel — Mon, 25 Mar 2013 12:53:44 GMT

Hi,

Can I suggest you try

Max(TOTAL datefield) as Max Month

Min(TOTAL datefied) as Min Month

Regardds,

Gabriel.

Re: How to find the MaxDate for a Calculated Expression

raajeshn — Mon, 25 Mar 2013 12:57:22 GMT

Thanks for the response Gabriel.

This will not work, as the Max(Total Dt) will give me the max value from the entire data - I need it specific to chart.

Thanks & Regards,

Raajesh N