Comparing Same Quarter during Prior Year

[Anonymous]

Hello Qlik Community – I’ve searched for hours but could not find a solution to what I’d imagine is a common task. I’m using a SQL database with dates in the format YYYY-MM (open to changing this if there’s a better practice). My QlikView script parses out the date from SQL like this:

LOAD

Left(DataYearMonth,4) as Year,
Q'&Ceil(Right(DataYearMonth,2)/3) as Quarter,
Date(Right(DataYearMonth,2)&'/1/2001','MMM') as Month

I then have 3 list boxes for Year, Quarter and Month so that the user can select a year and optionally, specific quarters or months within that year. I also have several text objects which show the period-over-period change, using a formula like this one:

=((Sum(Amount)-Sum({$<Year = {$(=Max(Year)-1)}>} Amount))/Sum({$<Year = {$(=Max(Year)-1)}>} Amount))

This works fine when I’m comparing say, Q2 2017 vs. Q2 2016, but I run into issues when I select only the most recent year (2018) from the listbox, and do not select a quarter or month. When I choose 2018, the most recent data I have is Q1, but unless I also select Q1 in the Quarter listbox, my formula compares Q1 2018 vs. all of 2017.

Can anyone help point me in the right direction here? I’m open to completely changing the date format in SQL, re-doing my forumula, etc., whatever it takes to make this as efficient and reliable as possible! Thanks!

[jwjackso]

I would suggest starting with a calendar, Creating A Master Calendar.  Link the date from your SQL database to the calendar.

You can create variable to use in set analysis, see the YTD and prior YTD examples below.

LET tCurrentYTD = Replace('MonthID = {"<=@(=Max(MonthID))"}, DateDT = {"<=@(=Max(DateDT))"}, FiscalYearNBR = {@(=Max(FiscalYearNBR))}','@','$');

LET tPriorYTD = Replace('MonthID = {"<=@(=Max(MonthID) - 12)"}, DateDT =  {"<=@(=AddMonths(Max(DateDT), -12))"}, FiscalYearNBR = {@(=Max(FiscalYearNBR)-1)}','@','$');

Your calculations then become:

Sum({<$(tCurrentYTD)>} Amount)

A calendar solves a lot of problems.

[Anonymous]

I added the Master Calendar and created variables, but I'm still having trouble...

Right now I only have full year 2017 and Q1 2018 data available, When the document opens, it selects 2018 from the Year field, without filters on the Quarter or Month fields. So the current YTD is the total Q1 2018 (which is correct), but when I try to compare it against the prior year, it uses the full year 2017 (since there are no filters on the quarter or month).

I'd like for QlikView to realize that I only have Q1 2018 data, so it should only use Q1 2017 to compare against.

[jwjackso]

In the master calender set the maxDate to the date of your last transaction.  Then when the time variables are created, the current year variables should be through the last transaction date and the prior year variables would be through that date - 1 year.

Can you show the variable calculations that you are using and how they are used in Set Analysis?