topic Re: Minimum Date In Chart in QlikView

Minimum Date In Chart

Fri, 12 Feb 2016 05:10:35 GMT

The age old question - how can I get the minimum date to use in an expression in a graph with a date dimension. Using set analysis only gives you the minimum of the date for the entire selection, however I need the minimum date for the time dimension of the graph, i.e. the graph has a time dimension of month, so for every month's bar in the graph, the minimum date should be the first of the month.

I need to get the opening customer count and this is the wrong expression:

sum( {< Period_date = {$(=min(Period_date)} >} cust_count)

Re: Minimum Date In Chart

Anonymous — Fri, 12 Feb 2016 07:39:07 GMT

try this?

=sum( {< Period_date = {'$(=Date(min(Period_date),'DD/MMM/YYYY'))'} >} cust_count)

Make sure Format of Period_date should be 'DD/MMM/YYYY'

Re: Minimum Date In Chart

Anonymous — Fri, 12 Feb 2016 08:03:09 GMT

By using the peek function to get the min date and by using the set analysis to get the min date vaules

Re: Minimum Date In Chart

MayilVahanan — Fri, 12 Feb 2016 08:55:29 GMT

If you are using Date dimension, then try like this

Create a variable like vMinPeriodDate = Min(Period_Date)

After that, use that variable in ur set analysis

=sum( {< Period_date = {'$(=Date($(vMinPeriodDate) ,'DD/MMM/YYYY'))'} >} cust_count)

Hope it helps

Re: Minimum Date In Chart

luciancotea — Fri, 12 Feb 2016 09:26:45 GMT

Set analysis is calculated ONCE, per chart, not for every value.

Re: Minimum Date In Chart

Fri, 12 Feb 2016 09:33:25 GMT

That's the problem. If set analysis was calculated for every value in the chart it would have worked.

Re: Minimum Date In Chart

jagan — Fri, 12 Feb 2016 09:40:22 GMT

Hi,

Set analysis is calculated once per chart, you have use Aggr() like below

Sum(Aggr(If(Period_date = Min(TOTAL <Month> Period_date), sum( cust_count)), Month, Period_date))

Note: Replace Month with your actual field name.

Hope this helps you.

Regards,

jagan.

Re: Minimum Date In Chart

Anonymous — Sun, 14 Feb 2016 23:26:55 GMT

Jagan, You rock man. Great answer. Thanks.