Actual vs Target(Budget ) -- Different Granularity

Thu, 18 Dec 2014 16:23:54 GMT

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 .

Different Granuality of Target
Consolidated Target

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

Target 1.

Region only

Target 2.

Region

　　　　 │

　　　　└→ Area

actual

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 ?

Re: Actual vs Target(Budget ) -- Different Granularity

Thu, 18 Dec 2014 16:29:07 GMT

forgot attachments.

Re: Actual vs Target(Budget ) -- Different Granularity

Thu, 18 Dec 2014 17:24:30 GMT

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

Re: Actual vs Target(Budget ) -- Different Granularity

Thu, 18 Dec 2014 18:00:20 GMT

Thanks ,

This really should work for the case 1. additional question , Is it a kind of common technique in qlik or BI world ?

topic Re: Actual vs Target(Budget ) -- Different Granularity in QlikView

Actual vs Target(Budget ) -- Different Granularity

Re: Actual vs Target(Budget ) -- Different Granularity

Re: Actual vs Target(Budget ) -- Different Granularity

Re: Actual vs Target(Budget ) -- Different Granularity