Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I see many topic of related mixed-Granularity ( especially Fact Table with Mixed Granularity ) .
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 .
1st Different ( more than one ) Granuality of Target
My actual data is like
Region
│
└→ Business Unit
│
└───Area
We have two different level of Target data one is Region, the other is Region-Area
Region only
Region
│
└→ Area
Business Unit
│
└→ Area
The user requirement is that , on a same chart ( example is pivot )
case 1
when user select Region + Area , they need to compare
Area Sum of Actual vs Region-Area Target
case 2
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.
Target Fomula
--> if(GetSelectedCount(Area)>0,sum({<Type={'Target_Region_Area'}>}amount) , sum({<Type={'Target_Region'}>}amount))
2nd Consoliated Target
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 ?
Ko
forgot attachments.
Hi Koichi,
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.
(not had a chance t look at no.2 yet sorry)
Hope that helps
Joe
Thanks ,
This really should work for the case 1. additional question , Is it a kind of common technique in qlik or BI world ?