topic Selecting Latest 2 Quarters in QlikView

Selecting Latest 2 Quarters

Thu, 07 Aug 2014 17:00:47 GMT

Hello Eveyone,

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.

Thanks.

Re: Selecting Latest 2 Quarters

JonnyPoole — Thu, 07 Aug 2014 18:01:56 GMT

i liked to do prior period SET ANALYSIS using a numerical counter of months ( or in this case quarters) that spans years.

here is an example of what you are asking

the SET ANALYSIS is:

$<QuarterCounter={$(vMaxQuarter),$(=vMaxQuarter-1)}>}

Where QuarterCounter is the running counter and vMaxQuarter is a variable =max( {1} QuarterCounter)

jonny

Re: Selecting Latest 2 Quarters

Thu, 07 Aug 2014 18:09:15 GMT

Thanks for the response.

But the max of the Quarter will always be 4. so how can this be done?

Re: Selecting Latest 2 Quarters

JonnyPoole — Thu, 07 Aug 2014 18:16:55 GMT

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 across years.

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.

Re: Selecting Latest 2 Quarters

Thu, 07 Aug 2014 18:24:58 GMT

Alternatively you could specify it in format YYYYQ.

As each quarter equals 3 months you can calculate quarter numbers with expression

Year(addmonths(Today(),-3*1)) & ceil(num(Month(addmonths(Today(),-3*1)))/3)

replacing *1 with the number of quarters you want to go back or forth

Re: Selecting Latest 2 Quarters

Thu, 07 Aug 2014 19:42:13 GMT

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),

Year(Today()),

max(FiscalYear)

)

sum({$<FiscalYear={$(=v_MaxFiscalYear - 1)}>} SomeField)

Re: Selecting Latest 2 Quarters

Thu, 07 Aug 2014 19:54:27 GMT

You could use the P() function to select only the possible quarters within the respective year. Something like this:

max({$<Quarter = P({$<YEAR=max(YEAR)>}) Quarter>})