# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor III

## 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!

1 Solution

Accepted Solutions
MVP

## 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)

15 Replies
MVP

## Re: Problem with set analysis

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
New Contributor III

## Re: Problem with set analysis

Hi,

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

Honored Contributor II

## 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)

New Contributor III

## 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!

New Contributor III

## 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!

New Contributor III

## 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)

MVP

## 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)

New Contributor III

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

New Contributor III

## 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: