topic Re: complex set analysis Date in QlikView

complex set analysis Date

Fri, 14 Feb 2014 13:26:45 GMT

Hi All,

I have an application with:

- orderID

- orderStatus (integer value)

- orderDate

- orderAmount

- ComparaisonDate (field not linked to the data model)

I need to calculate the sum of orderAmount where orderStatus has increased between a selected max(ComparaisonDate) and max(orderDate).

Regards,

Yacine

Re: complex set analysis Date

Fri, 14 Feb 2014 14:00:49 GMT

Try like below:

Sum({<OrderStatus={"$(=Max(ComparisonDate)-Max(OrderDate))"}>} OrderAmount)

Re: complex set analysis Date

Fri, 14 Feb 2014 14:15:47 GMT

thank you but this not works. as I know, I don't think comparing OrderStatus with dates is possible.

Re: complex set analysis Date

Fri, 14 Feb 2014 14:42:28 GMT

can you please post sample qvw.

Re: complex set analysis Date

Fri, 14 Feb 2014 15:30:21 GMT

What should happen if OrderStatus didn't increase? Should the sum of OrderAmount be set to zero?

And if OrderStatus DID increase, do you want the sum of OrderAmount over the range of months between compareDate and OrderDate? Maybe post an example of what should happen in both scenarios (OrderStatus increase and OrderStatus decrease/stay the same).

Re: complex set analysis Date

Fri, 14 Feb 2014 17:18:49 GMT

Sorry if I didn't explain very clearly.

Below an example of data :

orderID	orderStatus	orderDate	orderAmount
1		01/01/2014	300
1	30	02/01/2014	100
1	40	03/01/2014	400
1	50	04/01/2014	200
1	20	05/01/2014	500
1	60	06/01/2014	600
2	30	03/01/2014	700
2	40	04/01/2014	800
2		05/01/2014	900
2	70	06/01/2014	1000

In the my first post, I wrote only one part of the problem. now i'm explaining the entire query needed in another way:

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

Re: complex set analysis Date

Oleg_Troyansky — Fri, 14 Feb 2014 17:44:59 GMT

Yacine,

you will need to build a complex set analysis expression, with nested set analysis conditions. I'll explain the general idea here, and you will need to implement it with your data:

The "outer" condition will need to select those Orders that satisfy your "criteria", so the "outer" Set Analysis will be:

sum ( {<OrderID = {"=criteria"}>} Amount).

the "criteria" inside the search will have to include your actual condition of the increased status:

only( {<inner condition 1>} Order Status) > only ({<inner condition 2>}) Order Status)

The Inner conditions should reflect your date conditions:

Date = {$(=max(ComparisonOrderDate))}

Date = {$(=max(OrderDate))}

You'll need to ensure proper date formatting...

There might be a need in a lot of "tinkering" with the syntax and the proper formatting, but this is the general direction...

Come and learn Set Analysis with me at the Masters Summit for QlikView - coming soon to Chicago:

www.masterssummit.com

best,

Oleg Troyansky