Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Logic to Aggregate a specific Level Name

I’m hoping one of you can help me with the logic used to resolve this issue…

We pour Allocations starting at the Sales Division Level (Brand A/Brand B) down.  When they get pushed to the Sales Office Level any DM can pull available volumes from that level (it’s open to any DM).  If the Allocations are sitting at a Sales Office Level (DM) then only that DM can allocate that product to their Sales Groups (Sales Reps).

Level Name

Priority

 

Sales Division

1

Brand A/Brand B 

Commercial Unit

2

CU

Sales Office

3

District Mgr (DM)

 

I have a QLIK Sense view that has Available Allocation qty (Green column in the attached file).  In the view, the Available Allocation qty is poured at sales division level and then allocated to a district manager level.  If the available product qty is at the sales division level any DM can allocate from that pool.  If it’s at a DM level only that DM can allocate that product.

I would like to create logic that shows the Total Available Allocation for the Sales Division Level in a new column (blue column below in attached file) that any DM can see if they filter on just their information.  Again, the issue right now is if they filter on just there Sales Office they can only see the Available Allocation already assigned to their Sales Office but they lose visibility to all of the actual Sales Division Available Allocation product that any DM can pull from.

The attached file clearly spells it out but ultimately I am trying to create this view when filtered on a the DM Matt:

Level NameSales Division Material IDDM Name Allocated Available AllocationTOTAL AVAILABLE ALLOCATION
ResellerBrand AMen's AccessoriesTieMatt1120438

 

Any help/insights you can provide would be GREATLY appreciated!

 

4 Replies
Highlighted
Contributor
Contributor

Re: Logic to Aggregate a specific Level Name

 
Highlighted
Specialist III
Specialist III

Re: Logic to Aggregate a specific Level Name

Are you looking something like this?

tab1:
LOAD RowNo() As RowID,* INLINE [
    Level Name, Sales Division, Variety, Material, DM Name,  Allocated, Available Allocation
    Sales Office, Brand A, "Men's Accessories", Tie, Adam, 24, 0
    Reseller, Brand A, "Men's Accessories", Tie, Matt, 112, 0
    Sales Office, Brand A, "Men's Accessories", Tie, Mike, 499, 0
    Sales Office, Brand A, "Men's Accessories", Tie, Roy, 505, 0
    Commercial Unit, Brand A, "Men's Accessories", Tie, , 523, 0
    Commercial Unit, Brand A, "Men's Accessories", Clip, , 160, 0
    Sales Division, Brand A, "Men's Accessories", Tie, , "9,246", 2
    Sales Division, Brand A, "Men's Accessories", Clip, , "9,240", 40
    Sales Division, Brand A, "Men's Accessories", Handkerchief, , "1,080", 80
    Sales Division, Brand A, "Men's Accessories", Handkerchief, , "1,078", 316
];

Left Join(tab1)
LOAD Sum([Available Allocation]) As [TOTAL AVAILABLE ALLOCATION]
Resident tab1;
Highlighted
Specialist III
Specialist III

Re: Logic to Aggregate a specific Level Name

commQV35.PNGcommQV36.PNG

Highlighted
MVP
MVP

Re: Logic to Aggregate a specific Level Name

You can add the measure like below

=sum(total {<[DM Name]>}[Available Allocation])

If you don't want to change the value on other selection then you can add it in set analysis