Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Order of modifiers in element function, E() produce different results?

Hi,

I have been working with various set expressions with large data sets (10mill+ row) and I found that set expressions  produce various results depending of the order of set modifiers within element function, E() and the data model.

I have created a small sample set to reproduce the case with only 10 data rows,
using two different models.

Model 1: - single fact table
Model 2: - two tables linked with common field, OrderId.

I have used two different formulas to calculate the number of orders,
fullfilling the critreria, with the only difference being order of the set modifiers.

Formula 1:

count (DISTINCT {$<Status={"Completed"},OrderId = E({
1<OrderedBy={"Z"}> +1 <SubOrderCounter={">=2"}>}) >} OrderId)

Formula 2:

count (DISTINCT {$<Status={"Completed"},OrderId = E({
1 <SubOrderCounter={">=2"}> +1<OrderedBy={"Z"}> }) >} OrderId)

In Data Model 2 I get result = 5, while I was expecting =4, which would be inline with data model 1.

I have spend lots of time analysing the issue, but I can't figure it out why different results are produced.
Matematically - adding two different sets I form a larger union between them
and the order should not matter.
What have I omitted?

I would appreciate guidance from the experts in the community.

Best regards,
Adam


overview.png

Code for the data models:


QUALIFY *;
O:
Load * Inline
[OrderId, SubOrderCounter, OrderedBy, Status,Delivery
1, 1, A, Ordered, Preliminary
2, 1, B, Ordered, Preliminary
3, 1, C, Completed, Delivered
4, 1, D, Completed, Delivered
5, 1, E, Completed, Delivered
6, 1, F, Completed, Delivered
7, 2, A, Cancelled, Cancelled
8, 2, A, Completed, Delivered
9, 2, Z, Completed, Delivered
10,2, Z, Completed, Delivered
];

UNQUALIFY *;

Orders:
Load * Inline
[OrderId, Status,Delivery
1, Ordered, Preliminary
2, Ordered, Preliminary
3, Completed, Delivered
4, Completed, Delivered
5, Completed, Delivered
6, Completed, Delivered
7, Cancelled, Cancelled
8, Completed, Delivered
9, Completed, Delivered
10,Completed, Delivered
];

OrderData:
Load * Inline
[OrderId, SubOrderCounter, OrderedBy
1, 1, A
2, 1, B
3, 1, C
4, 1, D
5, 1, E
6, 1, F
7, 2, A
8, 2, A
9, 2, Z
10,2, Z
];

The samples app is attached as well.

Labels (1)
2 Replies
Highlighted
Contributor
Contributor

Hi,

I lokked into this problem a bit more. So far I have not been able to find an answer to this problem. The associated data model (two-tables) is not able to exclude the order that fulfills the criteria of element function.

I have even excluded  the searching with "", but results are the same.

count (DISTINCT {$<
Status={Completed}
,OrderId = E({
<OrderedBy={Z}>
+ <SubOrderCounter={2}>
})
>} OrderId)

Mathematically the + sign between modifiers builds a new union between the two modifiers. Order - orderedBy by Z having subOrderCounter = 2 should be in that set and hence be excluded.

The workaround is the use negating modifier outside the element function.
count (DISTINCT {$<
Status={Completed}
,SubOrderCounter-={2}
,OrderId = E({
<OrderedBy={Z}>
+ <SubOrderCounter={2}>
})
>} OrderId)

This gives the correct result.

 

Has anybody experienced similar issue and how to solve it?

@Henric_Cronström  Henric - would you be able to comment this?

Best regards,
Adam

Highlighted
Creator III
Creator III

shouldn't set operators e() be used like this: 

count (DISTINCT {$<Status={"Completed"},OrderId = E({1<SubOrderCounter = {">=2"}>}) + E({1<OrderedBy = {'Z'}>})>}OrderId)

 

additionnally... expected result would be 5? 

E({1<SubOrderCounter = {">=2"}>}) == {1,2,3,4,5,6}

E({1<OrderedBy = {'Z'}>}) == {1,2,3,4,5,6,7,8}

==> {1,2,3,4,5,6,7,8}

 

completed = {3,4,5,6,8,9,10}

 

==> orderID in final set = {3,4,5,6,8}

 

I have no idea what results are calculated with you expressions... but yes... they give unexpected results. (but your expressions are also unexpected 😉