they are all helpful but not 100% match with my situation. So would like have some advice from here.
Here I have two specific situation, I would like to highlight .
Different Granuality of Target
1st Different ( more than one ) Granuality of Target
My actual data is like
└→ Business Unit
We have two different level of Target data one is Region, the other is Region-Area
The user requirement is that , on a same chart ( example is pivot )
when user select Region + Area , they need to compare
Area Sum of Actual vs Region-Area Target
when user select only Region (btw region is "always select one value " ) , they need to compare
Regional Sum of Actual vs Region Target
I tried to put both those target into same fact table as actual data and distinguish by type ( actual , target_region, target_region_area )In attached example , case 1 works fine as when user select both Region and Area , qlikview filter and get only one target.
But case 2 when user select only "region" ,we can see target for both target_region and target_region_area .
as one of the solution, I can use if condition based on what is selected like below , but it is not preferable .
Can any one advise how I should handle this situation ? I don't even know if the "concentrated fact" is right decision or not , any suggestion is greatly appreciated.
We have regional target and Global target. If we stick to "concentrated fact" and keep all level ( region, Business Unit , Area ) in a fact table.Certain fact data will be stored more than one time.
ie ) USA is included in both "Global" and "North America" , so all USA data appears both under "Global" and "North America" .
In example , I put all level into a fact table which is easy to handle ,but concern is data size. example is oversimple and do not have many data ,but actual data can be more than Billion "actual" rows which may cause any issue like performance.
can anyone please advise how I should handle this from data model point of view ?
Re: Actual vs Target(Budget ) -- Different Granularity
It looks like you have an overall region target that is greater than the individual breakdown, presumably a floating overall target. One idea could be to make you region target more of a region topup target, instead of the overall number. Which would allow you to use the combination of the 2 for your overall region target but still be able to breakdown to the specific region ones.
I've just adjusted your target for North America in the excel, you have the option of doing this change in your source data (preferable) or if required can be done in QV by grouping up the area specific target into the region and netting the result.