Comparing monthly sales to quarterly targets

[AdamG1]

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.

[ashishkalia]

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.

[AdamG1]

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 · Accepted solution]

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