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: 
epar5lcd
Contributor III
Contributor III

Time series dimension and measures from separate tables

I am running into a few issues related to handling of time series.

I am tracking the dates a record achieves a number of goals. These goals can be achieved in any particular order. Multiple goals can be achieved on the same date. My metrics date as far back as 1985 to YTD. Below is a sample of my data set. I have also attached a more comprehensive table to reproduce my problems.

RecordGoal_1Goal_2Goal_3
A7/14/19958/19/20086/18/1992
B12/19/198512/19/1985
C3/18/2018
D1/1/20152/1/20154/1/2015

My objectives:

Group my data by each Goal & Fiscal Calendar Year displayed as a bar chart, with a drill-down to Fiscal Calendar Months Oct to Sep including NULL values.

The basic approach:

Use [Date Goal_#.autoCalendar.Year] with a drill-down to [Date Goal_#.autoCalendar.Month] as the dimension and count(Goal_#) as the measure.

My problem:

I want to achieve a hybrid rendering of normal and continuous scales for datetime as a dimension. If we cosider Qlik's normal rendering of dates we have...

Wanted behavior: Thick bars with a view pane scroll bar. Each bar is labeled with year, then by month name (MMM) on drill-down.

Unwanted behavior: Years/months with NULL values are completely excluded. Months with no achievements mean something!


Now lets consider the continuous scale rendering...

Wanted behavior: Complete time series, with years/months with no activity appearing empty.

Unwanted behavior: Measure bars appear as lines, makes selection for drill down cumbersome. View pane scroll bar is disabled, entire time series is condensed into view area. Drill-down month labels appear as numbers.


My approach:

Create a secondary table with the complete time series beginning 10/1/1985 through 9/30/YTD. I am using the following script in the data load editor:

SET FY_StartMonth = 10;

LET StartDate = Num(MakeDate(1985,10,1));

LET EndDate = Floor(AddMonths(YearEnd(AddMonths(Today(),3)),-3));


FiscalCalendar:

LOAD

*,

Dual('Q' & Ceil(FY_Mnum/3), Ceil(FY_Mnum/3)) AS FY_Quarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FY_Mnum) AS FY_Month; // Fiscal Calendar Month

LOAD

*,

Year(Date) AS Year, // Standard Calendar Year

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter

Month(Date) AS Month, // Standard Calendar Month

YearName(AddMonths(Date,3)) AS FY,  // Fiscal Calendar Year

Mod(Month(Date) - $(FY_StartMonth), 12)+1 AS FY_Mnum;  // Fiscal Calendar Month Number

LOAD

RangeSum(Peek('RowNum'), 1) AS RowNum,

Date($(StartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date

AutoGenerate EndDate - StartDate + 1;

This script creates the groupings to adjust the timeseries to my Fiscal Calendar. I am associating my Goal_#(date) with my Date(FiscalCalendar). Then using [FY.autoCalendar.Year] with a drill-down to [FY.autoCalendar.Month] as the dimension and count(Goal_#) as the measure.

An added benefit of this approach is that I should be able to show all my Goal_#s as measures to the same dimension allowing my users to view all on the same chart vs creating charts for each individual Goal_#.

Observed Errors:

The results appear as a count value of 566 for every year from 1986 to 2018. Drill down to month assigns all values to Jan, no other month appears

Help!

Any comments on why this approach is failing is appreciated. I am not married to my approach and willing to change it to whatever helps achieve the goal. I do have a few restrictions though. Data is coming in from an external Oracle connection that updates weekly so I want to minimize transformations on the main table. If transformations are necessary they must be fully automated. I also have additional data tied to my record that is visualized and should appear filtered as the data is drilled down. I think my current approach of using a secondary table prevents this filtering but I can't tell since it isn't working as expected.

Thanks!

1 Solution

Accepted Solutions
epar5lcd
Contributor III
Contributor III
Author

Unpivoting the source data using the crosstable function appears to resolve the problem. A quick video tutorial is available here.

The process changes the table from a 'wide' format to a 'long' format. All dates are placed into a single column creating a table similar to below:

Unpivoted Table.png

The long format allows to make the correct association between the data table and the master calendar table. Once the two tables are associated we can use the master calendar date as the dimension and the measure type as the measure (eg. Measure='Goal_2'). With this approach the aggregation type is now a SUM of the measure type instead of a COUNT of the date frequency. For some reason the values aggregate as a negative so the whole expression needs to be turned into a negative to appear as a positive index (eg. -Sum(Measure='Goal_2')) .

To display the correct months on the drill down you actually need to source two different autoCalendars generated from the Master Calendar: FY.autoCalendar.Year AND Date.autoCalendar.YearMonth. Using FY.autoCalendar.Month as described above will aggregate the total to the month of  Jan. Using Date.autoCalendar.YearMonth on the other hand should display the correct distribution AND display labels as 'YYYY-MMM' which may help users keep track of correct dates as the fiscal calendar spans two different years.

One thing to note is that the month may auto sort incorrectly on the drill down. To get it to sort correctly go to the "Sorting" section for the table/chart. Turn off the auto sorting. A drop down menu will appear that will allow specifying the custom sorting for each level in the drill down. Select the 'Month' definition, "Sort numerically" and use the drop down menu to set it to "Ascending".

View solution in original post

1 Reply
epar5lcd
Contributor III
Contributor III
Author

Unpivoting the source data using the crosstable function appears to resolve the problem. A quick video tutorial is available here.

The process changes the table from a 'wide' format to a 'long' format. All dates are placed into a single column creating a table similar to below:

Unpivoted Table.png

The long format allows to make the correct association between the data table and the master calendar table. Once the two tables are associated we can use the master calendar date as the dimension and the measure type as the measure (eg. Measure='Goal_2'). With this approach the aggregation type is now a SUM of the measure type instead of a COUNT of the date frequency. For some reason the values aggregate as a negative so the whole expression needs to be turned into a negative to appear as a positive index (eg. -Sum(Measure='Goal_2')) .

To display the correct months on the drill down you actually need to source two different autoCalendars generated from the Master Calendar: FY.autoCalendar.Year AND Date.autoCalendar.YearMonth. Using FY.autoCalendar.Month as described above will aggregate the total to the month of  Jan. Using Date.autoCalendar.YearMonth on the other hand should display the correct distribution AND display labels as 'YYYY-MMM' which may help users keep track of correct dates as the fiscal calendar spans two different years.

One thing to note is that the month may auto sort incorrectly on the drill down. To get it to sort correctly go to the "Sorting" section for the table/chart. Turn off the auto sorting. A drop down menu will appear that will allow specifying the custom sorting for each level in the drill down. Select the 'Month' definition, "Sort numerically" and use the drop down menu to set it to "Ascending".