Aggregation Distribution across buckets in Qlik Sense

[zabiullahsyed]

Dear Users,

I am building an App to Allocate Cases based across different dimensions and based on available cases.

The Table 1 looks like below: Here we have CS To Be Reviewed and CS Approved as Measures and are at Plant, Customer Number and Product Code level. We derive on these first three dimensions joins from source tables.

these measure 

Now I have a second table where we want to drill down to more detailed dimensions which is sales order and Item.

Here we try to derive a measurement called Allocation Decision. If you see the CS To Be Reviewed is distributed correctly from table 1 which is 3 in total. However CS Approved is assigns to each line where as the total of CS Approved is 1 in table 1. This could be due to the fact of detailed dimensions in table 2.

What I want to achieve is:

To Take the Sum of CS Approved from table 1 across those 3 dimensions and start allocating to table 2 lines.

First line should be - SUM(CS Approved across those 3 dimensions which is  to be 1) compare to CS To Be Reviewed for first line in table 2 which is 1. And if CS Approved is >= CS To Be Reviewed then Allocation decision is Full Release.

Second Line - Now the SUM(CS Approved across those 3 dimensions which is  left with 0 after above allocation) compare to CS To Be Reviewed for first line in table 2 which is 1.  Here the Allocation decision is Full Reject.

Third Line will also be Full Reject.

There will be situations where CS Approved will be less than CS To Be Reviewed then Allocation Decision will be Partial Release.

Can you please help with the expression logic or how to achieve this ?

Many Thanks.

Regards

Zabi