Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Selecting Latest 2 Quarters

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

6 Replies
JonnyPoole
Employee
Employee

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

Not applicable
Author

Thanks for the response.

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

JonnyPoole
Employee
Employee

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.

Not applicable
Author

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

Not applicable
Author

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)

Not applicable
Author

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