Comparing sales to total targets when some regions don't have sales
I have a problem structuring my tables for sales vs targets and am after some advice.
All I want is total sales in one column and total target in another.
Suppose you have a table containing region, product and product_target, so it starts like this:
Key Region Product Target
1/A 1 A 1,000,000
2/A 2 A 1,000,000
...
1/B 1 B 500,000
2/B 2 B 500,000
...
My sales table also has the same key as above
.
If I have sales of all products in all regions, then comparing sales to targets is easy. Because I can simply sum up the targets.as there are no missing regions.
However, if for Product A there are only sales in Region 1, then there won't be a corresponding 2/A record in the sales table. Therefore, when I try to sum up the targets for product A, it always omits 2/A. In short, it is only adding up the targets in the regions where a sale exists.
How can I get round this, so that regardless of whether or not a region has sales, the target will still always add up to the overall target?
I hope I've explained myself well enough and look forward to reading your responses. I'm sure there must be some simple and clever way of doing this.
This should be entirely possible - the below is un-checked as I'd ideally need an example to work from:
If not I'd start by looking to create a non-associated compound dimension in the load script to drive you're table dimension (Resident Load & Concatenate) and then in your table expressions add a 'if' to associate you're expression to each entry in the compound dimension.
To create the compound dimension:
Compound_TMP:
Load Distinct Key as CompundDim
Resident Sales;
Concatentate (Compound_TMP)
Load Distinct Key as CompounDim
Resident Targets;
Compound:
Load Distinct CompoundDim
Resident Compound_TMP;
Drop Table Compound_TMP;
You'll then have a list of each possible Key value to drive the dimension.
To sort the expression: =if(Key=CompoundDim,sum(Sales),0) or if(Key=CompoundDim,sum(Targets),0).
As I said 'un-chaecked' so if it doesn't work fiddle with the syntax and change the 'IF' orders etc. If it still doesn't play ball add an extra expression of ='' with a header of ='' ; this will add a blank column that applies to each dimensional value thus forcing it to display (great for showing stock levels even when there's no stock of a particular item).
First, thank you very much for your help. I follow you up to the expression bit.
I wonder if you'd be so kind to take a look at the attached qvw file, which illustrates exactly what I want to do. Could add the correct expression to the bottom right table?
I wasn't able to attach the file to this reply, so I've attached it to my original message, above.
The image top left shows you the two reports I'd like to get out of the data.
Try the attached, it is a bit less complex than what Matt suggested but should still work for you. Just remember to use the TargetPeriod as your time dimension in your charts. Also, make certain you don't have any Sales for products or periods you dont have a Target for.
