Problem with set analysis

[steve_br]

Hi all,

For days now I am struggling to get my expression working and need your help. I'd like to calculate the percentage of orders that have higher than the requested quantity. This should be done only for orders with the same unit of measure and displayed in pivot table per department. Below a simplified script:

Data:
Load*Inline
[
Order|Item|Requested|Requested_Unit|Ordered|Ordered_Unit|Department
O100|I10|2|T|3|T|Materials
O100|I20|2000|KG|3|T|Materials
O200|I10|30|KG|70|KG|Materials
O200|I20|50|KG|50|KG|Packaging
]
(Delimiter is '|');

Table:
Load
    If(Requested_Unit=Ordered_Unit, 'Same_unit', 'Different_unit') as Unit, 
    Order,
    Item,
    Requested,
    Requested_Unit,
    Ordered,
    Ordered_Unit,
    Department
Resident Data;
Drop Table Data;

For order O100, only the first line should be taken into account, as the second line has different units (KG and T).

For order O200 both lines should be considered since the unit of measure is the same (KG).

Therefore, the result should be:

Department Materials: 100% (2 orders with Ordered > Requested out of 2 orders in total)

Department Packaging: 0% (0 orders with Ordered > Requested out of 1 orders)

Please note that all data needs to be loaded and nothing can be excluded in the script. I tried with this expression in pivot table...

Count({<Unit={'Same_unit'}, Order={"=Sum(Ordered) > Sum(Requested)"}>}Distinct Order)
/ 
Count({<Unit={'Same_unit'}>}Distinct Order)

...but it gives totally wrong results. It does not consider O100 at all (probably because one of the entries has different measures) and counts O200 as Ordered > Requested for both departments (not sure why, maybe it takes the sum of quantity for the entire order and not per department).

I really hope that somebody can help me out here. Cheers!

[swuehl]

In your Table LOAD, create a primary key like

Table:

LOAD

     Recno() as RecID,

     ....

Then try

1. Count({<Unit={'Same_unit'}, RecID={"=Sum(Ordered) > Sum(Requested)"}>}Distinct Order) 
2. /  
3. Count({<Unit={'Same_unit'}>}Distinct Order) 

[Anonymous]

Hi,

See attachement, but basically you want to use aggr function in situation like this.

[agigliotti]

maybe this:

if( Sum( {<Unit={'Same_unit'}>} Ordered) > Sum( {<Unit={'Same_unit'}>}Requested), Count( DISTINCT Order ), 0 )

/ 

Count({<Unit={'Same_unit'}>}Distinct Order)

[steve_br]

Hi Stefan,

this appears to work great with the data that I posted. I have to test it with the real application as well, unfortunately I am not allowed to change the script there and it might take weeks (if not months) to get the approval from IT. Is there any way to achieve this functionality in the front end?

Thank you very much for your help!

[steve_br]

Hi Joonas,

thank you for your time! I am working with Qlik Sense and cannot open the file you posted. Could you please copy the expression you used here. Thanks!

[steve_br]

Hi Andrea! Thanks for your input! This works with the posted test data, however, if I add the following lines to the script

O300|I20|2000|KG|3000|L|Materials

O300|I20|2000|KG|3000|KG|Packaging

it would show:

Materials 150% (100% would be correct as O300 should not be considered -> different units)

Packaging 100% (50% would be correct as O300 should now be taken into account)

[sunny_talwar]

May be try this

Sum(Aggr(If(Sum(Ordered) > Sum(Requested), Count({<Unit={'Same_unit'}>}Distinct Order)), Order, Item, Department))/Count({<Unit={'Same_unit'}>}Distinct Order)

[Anonymous]

Oop sorry, here's Sense version.

Experssion is:

sum( 
aggr(Count(TOTAL{<Unit={'Same_unit'}, Order={"=Sum(Ordered) < Sum(Requested)"}>}Distinct Order) , Order))
 /  Count({<Unit={'Same_unit'}>}Distinct Order
 )  

You need TOTAL to tell Qlik not to care about dimensions in chart when calculating things in Order level.

[steve_br]

Thanks Sunny,

I tested the formula with the real data and it identifies the right orders (which is great), but it appears that it does not count distinct. I updated the script below to reflect this situation. If I add one more item to order O100, the result for department Materials goes up to 150%.

Data:  
Load*Inline  
[  
Order|Item|Requested|Requested_Unit|Ordered|Ordered_Unit|Department  
O100|I10|2|T|3|T|Materials
O100|I30|2|T|6|T|Materials  
O100|I20|2000|KG|3|T|Materials  
O200|I10|30|KG|70|KG|Materials  
O200|I20|50|KG|50|KG|Packaging
O300|I10|2000|KG|3000|L|Materials
O300|I20|2000|KG|3000|KG|Packaging
]  
(Delimiter is '|');  

Table:  
Load  
    RecNo() as RecID,
    If(Requested_Unit=Ordered_Unit, 'Same_unit', 'Different_unit') as Unit,   
    Order,  
    Item,  
    Requested,  
    Requested_Unit,  
    Ordered,  
    Ordered_Unit,  
    Department  
Resident Data;  
Drop Table Data;