Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Problem with set analysis

How about this?

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


Capture.PNG

15 Replies
MVP
MVP

Re: Problem with set analysis

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

Re: Problem with set analysis

Hi,

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

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

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

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

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

joonasjaspi
New Contributor III

Re: Problem with set analysis

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

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;