Aggregation Distribution across buckets in Qlik Sense

zabiullahsyed — Sat, 16 Nov 2024 02:35:40 GMT

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

topic Aggregation Distribution across buckets in Qlik Sense in App Development

Aggregation Distribution across buckets in Qlik Sense