Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Actual vs Target(Budget ) -- Different Granularity

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 .

  1. Different Granuality of Target
  2. 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 ?

Ko

3 Replies
Not applicable
Author

forgot attachments.

Not applicable
Author

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

Not applicable
Author

Thanks ,

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