I have 2 fields; Quarter which includes 1,2,3,4 and another field YEAR which includes YEAR. I want to use those 2 fields to get the data for the latest 2 quarters in charts which is 2014 Q1 and 2014 Q2 by using set analysis and this has to be dynamic, so when the new data populates for the new quarter which is 2014 Q3, the chart will show data for 2014 Q3 and 2014 Q2.
In the example there is Quarter (1 , 2, 3, 4) and in the load i added a brand new QuarterCounter field that assigns (1,2,3,4,5,6 etc... ) in progression to each quarter from earliest to latest time period.
So that allows me to reference a quarter numerically acrossyears.
as you can see in the load script you need a calendar table that is sorted from earliest to latest with all the years and quarters in order to do this. It means adding a calendar table to the data model but i think that is a good thing and it certainly makes YTD expressions easier.
Build a calendar table if you didn't already do that. The Date field in your data will tie to the dates in your calendar. You can add additional fields to your calendar for whatever you need to filter on or do date math with. For example:
Date, CalYear, FiscalYear, FiscalQtr, FiscalYYYYQ, FiscalEOYDate, FiscalMonth, etc.
Most of the time business data contains future dates. To handle this set up some macros/variables to get the max non-future date in your filtered data. That is better than using today() or max(date)since users may select a year range outside the current year. You can use that in your set analysis or conditions... for example:
v_MaxFiscalYear = if (Year(Today()) < max(FiscalYear),