Announcements
cancel
Showing results for
Did you mean:
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
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)>}.

3 Replies
Partner - Creator

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

 Qtr Target Q1-19 50 Q2-19 90 Q3-19 150 Q4-19 200

 MonthYr Sales Jan-19 10 Feb-19 2 Mar-19 12 Apr-19 13 May-19 133 Jun-19 42 Jul-19 12 Aug-19 111 Sep-19 90

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.

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"?

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

Community Browser