12 months trailing filter

[zagzebski]

I have a dashboard with lots of charts already built. Now I have been asked to add a filter that would be a 12 months trailing button. Basically it would filter on the most recent 12 months and then the previous 12 months from that, etc. For example

August 2013-July 2014 (expression 1)

August 2012-July 2013 (expression 2)

etc

Most of my charts have two or three expressions for current year and past year(s) petty basic.

So my question is:

1. What is the best way to incorporate this filter. Do I have to build an if statement of some kind so that if selected do..., otherwise do...

Or is it better to show and hide charts based on this selection?

All my expressions have max(year) in them so I don't see how I can use a last 12 months filter within those expression.

Thanks in advance...

[JonnyPoole · Accepted solution]

For prior period filters one good way is to build a calendar table into your data model. The calendar table would have year, month, quarter and date.   It would also have a few boolean flags, like Current Month, Current Quarter , Current Year , Prior Year etc... whose values are 1 or 0 depending on whether the date is in the current month etc...

Use date functions and arithmetic functions to compute the flags.

You could do the same flag approach in the table to create the  Last12Months , PriorYearLast12Months flags that you are after.

Once that table with the flags are in place, the expression is a very straight forward SET ANALYSIS statement in the chart:

sum(  {<$ 12MonthFlag={1}>}  <expression> )

sum(  {<$ PriorYear12MonthFlag={1}>}  <expression> )

[zagzebski]

Thanks - I am going to put those flags in.

The problem I have is I feel like I have to build a duplicate for every chart I have now - or is it better to create some kind of if statement into every expression to address a user clicking the Trailing Twelve Months button.

[JonnyPoole]

It depends. 

When you build the flag i would suggest creating it without an 'else' value ie:

if(  < date is within last 12 months>  , 'True') as  12MonthFlag,

if(  < date is within prior year last 12 months>  , 'True') as  PriorYear12MonthFlag,

this the list box for the flag works like an on/off button.

But was is the behaviour you need to see when the 12Monthflag is applied ?

     1. Do you need to automativally display the current 12MonthFlag and the prior12MonthFlag side by side in charts ?

     2. Do you need to give users the option of showing/hiding the prior12MonthFlag

There are a few ways to proceed. Most ways will only need 1 chart, not 2 although the expressions will often have IFs or SET ANALYSIS with variables.

If you want to keep your chart expressions very basic and you only have to worry about 12Month and PriorYear12Months, you can take the flags a step further and create a special lookup table in the data model to create a calculated field that displays 12Months or prioryear12Months to the users as options to select.

assuming you have built your calendar with a 12MonthFlag and priorYear12Monthflag with 1 or 0 in both and assuming its joined on the field 'date' and your calendar table is 'DateCalendar' , create a lookup table like this.

CYPY12MonthListBox

Load

     date,

     '12 Months'  as CYPYListbox,

resident  DateCalendar

where 12MonthFlag=1;

concatenate (CYPY12MonthListBox)

Load

     date,

     'Prior Year12 Months'  as CYPYListbox,

resident  DateCalendar

where PriorYear12MonthFlag=1;

Then you can use the new field CYPYListbox in a listbox or a dimension in a chart . This will give the users the ability to select which period they want to display and the result will display in a chart. Optionally include an 'all dates' in the field by adding this to to the new table.

concatenate (CYPY12MonthListBox)

Load

     date,

     'All Dates'  as CYPYListbox,

resident  DateCalendar;

This would give you 1 chart version of the chart and basic expressions.  The code is done in 1 place rather than repetitive IF statements. 

There could be other neater UI options as well that don't have a footprint in the data model.