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: 
BLeaG
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
BLeaG
Contributor
Contributor
Author

 
Saravanan_Desingh

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;
Saravanan_Desingh

commQV35.PNGcommQV36.PNG

Kushal_Chawda

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