Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
AdamG1
Contributor II
Contributor II

Comparing monthly sales to quarterly targets

Hi All,

I have two fact tables (Sales and Sales_targets), a master calendar and a listbox "Month" filter. Problem is, sales are monthly (i'm currently using monthstart to link them with the cal) and targets are quarterly. What can I do to make both work with "Month" selection? I.e. I'd like selecting Jan show Jan sales with Q1 target.

Do I load quarterly target values only for last month each quarter? This won't work as first two months will have no target assigned. Do I link the targets to quarters in master cal? This will multiply the value, producing incorrect results. Is there some way to script specific charts/objects to map selected month(s) to their "quarter ends"?

Any help will be welcome. I'd prefer a hint I can work with and understand myself though than "here's my code" answers.

 

1 Solution

Accepted Solutions
AdamG1
Contributor II
Contributor II
Author

To anyone struggling with similar problems: indirect set analysis is the way to go. I've solved my "select whole quarter when at least one month is selected" with {<Month=, Quarter=P(Quarter)>}.

View solution in original post

3 Replies
ashishkalia
Partner - Creator
Partner - Creator

Based on the above information, I think you will  be having sales and Qty Targets as below

QtrTarget
Q1-1950
Q2-1990
Q3-19150
Q4-19200

 

MonthYrSales
Jan-1910
Feb-192
Mar-1912
Apr-1913
May-19133
Jun-1942
Jul-1912
Aug-19111
Sep-1990

 

Just make an inline table to map Q1, Q2, Q3, Q4 to the respective month and link that month field with your calendar month. While displaying Targets use set analysis to show it for the quarter with By-passing of months.

AdamG1
Contributor II
Contributor II
Author

Many thanks for the advice, Ashish. I did go with a similar approach and now I have a correcly working dataset with targets appearing only on each quarter's start (e.g. January for Q1), while sales are present for each month. I'm struggling how to phrase the set analysis formulas correctly though.

If I include months with target values in selection - everything is fine (Jan shows Q1 target). If I skip them (select Feb or March), no value is produced. What formula/approach, set analysis or otherwise, can tell QlikView to go and pick "first month of selected month's quarter" value? And in a way that doesn't result in duplicates?

In non-QV terms I would see something like "for each unique quarter selected (indirectly, via Months filter), sum that quarter's targets" or at least "sum targets from months equal or earlier than selected"? 

AdamG1
Contributor II
Contributor II
Author

To anyone struggling with similar problems: indirect set analysis is the way to go. I've solved my "select whole quarter when at least one month is selected" with {<Month=, Quarter=P(Quarter)>}.