Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

hkg_qlik
Contributor II

Display 12 Months (Straight Table)

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,

H

Tags (3)
1 Solution

Accepted Solutions

Re: Display 12 Months (Straight Table)

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)

8 Replies

Re: Display 12 Months (Straight Table)

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

hkg_qlik
Contributor II

Re: Display 12 Months (Straight Table)

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,

H

Re: Display 12 Months (Straight Table)

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)

jaimeaguilar
Valued Contributor II

Re: Display 12 Months (Straight Table)

Hi,

try this

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

Regards

Re: Display 12 Months (Straight Table)

Hi

Try like this

Round(Sum({<

  Year=,

  Month=,

  Week=,

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

Not applicable

Re: Display 12 Months (Straight Table)

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

Not applicable

Re: Display 12 Months (Straight Table)

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

Not applicable

Re: Display 12 Months (Straight Table)

thanks helpful

Community Browser