topic Re: Display 12 Months (Straight Table) in QlikView

Display 12 Months (Straight Table)

hkg_qlik — Tue, 07 Jan 2014 19:43:20 GMT

Hi,

Users are only interested to see last 12 month of data as of the Current month which is JAN 2014. This should be automated as once we move to FEB 2014 the table should adjust itself to 12 months only.

Regards,

Re: Display 12 Months (Straight Table)

Nicole-Smith — Tue, 07 Jan 2014 19:46:37 GMT

If you're using QV11, you can put a Dimension Limit on your Date field to only view Largest12 dates:

Re: Display 12 Months (Straight Table)

hkg_qlik — Tue, 07 Jan 2014 20:05:06 GMT

Hi Nicole,

Dimension limit will not work in this scenario because we are not evaluating any values.

For eg: Current Month is Jan 2014

So the data should be displayed starting DEC 2012, JAN 2013, FEB 2013.....JAN 2014

Once we move to FEB 2014 month the display will changes :

starting JAN 2013, FEB 2013........FEB 2014

Regards,

Re: Display 12 Months (Straight Table)

Nicole-Smith — Tue, 07 Jan 2014 20:10:58 GMT

You can add set analysis to your expression (suggested):

{<YourDateField={'>=$(=monthstart(AddMonths(today(),-13)))'}>}

which will give you anything greater than or equal to 12/1/2012

Or on your dimension:

if(YourDateField >= monthstart(AddMonths(today(),-13)), YourDateField)

Re: Display 12 Months (Straight Table)

jaimeaguilar — Tue, 07 Jan 2014 20:11:10 GMT

Hi,

try this

=sum({$<MonthYear={'<=$(=max(MonthYear))>=$(=addmonths(max(MonthYear),-12))'} >} YourField)

Regards

Re: Display 12 Months (Straight Table)

MayilVahanan — Tue, 07 Jan 2014 20:15:01 GMT

Try like this

Round(Sum({<

Year=,

Month=,

Week=,

DateNum= {'>=$(=(Num(MonthStart(Today(), -11))))<=$(=(Floor(MonthEnd(Today()))))'}>}Allowed))

Re: Display 12 Months (Straight Table)

Tue, 07 Jan 2014 20:29:09 GMT

this is the largest value within the dimensions so wont work.

Re: Display 12 Months (Straight Table)

Tue, 07 Jan 2014 20:34:14 GMT

In my master calendar I load a flag to mark each date if it falls within the last 12 months. This is a master calendar script that is published here on the community. So in my set analysis I ad _R12 = 1 and it will only pick up the last 12 months. It will update the flags each reload.

//Rolling

     If( TempDate > addmonths($(CurrentDate),-12) and TempDate <= Today(),1) as _R12, // Rolling 12
    If( TempDate > addmonths($(CurrentDate),-2) and TempDate <= Today(),1) as _R2, // Rolling 2
    If( TempDate > addmonths($(CurrentDate),-3) and TempDate <= Today(),1) as _R3, // Rolling 3
    If( TempDate > addmonths($(CurrentDate),-6) and TempDate <= Today(),1) as _R6, // Rolling 6
    If( TempDate > addmonths($(CurrentDate),-24) and TempDate <= addmonths($(CurrentDate),-12),1) as _R13_24// Rolling 13 -24

Re: Display 12 Months (Straight Table)

Tue, 16 Jun 2015 08:15:44 GMT

thanks helpful