15 Replies Latest reply: Aug 27, 2017 10:51 AM by Steve Br.

# Problem with set analysis

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:
[
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:
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!

• ###### Re: Problem with set analysis

In your Table LOAD, create a primary key like

Table:

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
• ###### Re: Problem with set analysis

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!

• ###### Re: Problem with set analysis

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)

• ###### Re: Problem with set analysis

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:
[
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:
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;
```
• ###### Re: Problem with set analysis

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

• ###### Re: Problem with set analysis

This is great, exactly the same result as RecNo() in the script. Thank you very much Sunny, I appreciate your help and time!!!

• ###### Re: Problem with set analysis

I must also mention that the solution provided by Stefan works great as well, but unfortunately I cannot use it because of restrictions coming from our IT.

• ###### Re: Problem with set analysis

Hi,

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

• ###### Re: Problem with set analysis

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!

• ###### Re: Problem with set analysis

Oop sorry, here's Sense version.

```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.

• ###### Re: Problem with set analysis

Thank you for the effort!

I checked this with my real data and it does not quite work. I slightly updated the load script (posted above) to better reflects the real situation. The result should be Materials: 100%, Packaging: 50% and your formula returns 150% and 0% respectively.

• ###### Re: Problem with set analysis

maybe this:

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

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

• ###### Re: Problem with set analysis

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)

• ###### Re: Problem with set analysis

Hi Steve

Please take a look at the sample qvf:

I used the latest sample data you've given.

• ###### Re: Problem with set analysis

Thank you for the effort. It appears that your expression does not count distinct orders, i.e. adding a line like this:

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

would count O300 twice instead of only once.

Anyways, the solution provided by Sunny above works. Thanks again for your reply.