Logic to Aggregate a specific Level Name

[BLeaG]

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 Name	Sales Division		Material ID	DM Name	Allocated	Available Allocation	TOTAL AVAILABLE ALLOCATION
Reseller	Brand A	Men's Accessories	Tie	Matt	112	0	438

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

[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]

[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