Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

set analysis complex

Below an example of data :

orderIDorderStatusorderDateorderAmount
101/01/2014300
13002/01/2014100
14003/01/2014400
15004/01/2014200
12005/01/2014500
16006/01/2014600
23003/01/2014700
24004/01/2014800
205/01/2014900
27006/01/20141000

I need 2 expressions :

Expression 1) count distinct OrderId where :

- OrderStatus at orderDate > OrderStatus at ComparaisonOrderDate

- orderStatus is not null at the two periods.

Expression 2) sum of orderAmount at orderDate - orderAmount at ComparaisonOrderDate

where

- OrderStatus at orderDate > OrderStatus at ComparaisonOrderDate

- orderStatus is not null at the two periods.

Examples 1:

when I select ComparaisonOrderDate=02/01/2014 and  orderDate=04/01/2014

Expression 1 should be: 1 (only orderID=1)

because 50 > 30 and 30 is not null and 50 is not null

Expression 2 should be: 100

200-100

Examples 2:

when I select

ComparaisonOrderDate=03/01/2014 and  orderDate=06/01/2014

Expression 1 should be: 2 (orderID=1 & 2)

because in orderID=1, 60 > 40 and in orderID=2, 70 > 30

Expression 2 should be: 500

(600-400) + (1000-700)

Regards,

Yacine

1 Reply
Employee
Employee

Re: set analysis complex

Hi

Set-expressions cannot do the job here. You want to compare date-pairs and status-pairs per each orderID. A set would apply to the whole selection and would not be different in each row.

Here is my solution, if I got you right:

Load the order table again 1:1 but with a prefix comparision. Link the copy with the original via the common orderID

The two expressions are:

LET Expr1 = 'If(orderStatus > comparison.orderStatus AND Len(orderStatus) AND Len(comparison.orderStatus), Count(orderID))';

LET Expr2  = 'If(orderStatus > comparison.orderStatus AND Len(orderStatus) AND Len(comparison.orderStatus), Sum(orderAmount) - Sum(comparison.orderAmount))';

If you create a chart which has %orderID as a dimensionality, you can put those formulas as a expression. If you want to get the grand total of the two expressions outside such a chart dimensionality, create the sum or count based on an Aggr() pivot, creating the dimensionality ad-hoc.

Count(Aggr($(Expr1), %orderID))

or

Sum(Aggr($(Expr2), %orderID))

Hope this helps

Community Browser