topic Re: Orderbook evolution Chart in QlikView

Orderbook evolution Chart

salto — Mon, 19 Sep 2016 14:22:08 GMT

Hello Community,

Once again I seek for your invaluable help.

I have a table like this:

Date	OrderDate	ShipmentDate	Amount
01/09/2016	01/09/2016	03/09/2016	27
01/09/2016	01/09/2016	05/09/2016	70
02/09/2016	02/09/2016	06/09/2016	85
02/09/2016	02/09/2016	04/09/2016	63
03/09/2016	03/09/2016	06/09/2016	31
04/09/2016	04/09/2016	07/09/2016	20

My request is to create a chart that shows the evolution of the orderbook: the SUM of Amount ordered and Not Shipped in a per-day basis.

The result (Orderbook) should show something like this:

Date	Orderbook
01/09/2016	97 (+27+70)
02/09/2016	245 (+148)
03/09/2016	249 (+31-27)
04/09/2016	208 (+22-63)
05/09/2016	138 (-70)
06/09/2016	22 (-85-31)
07/09/2016	2 (-20)

The expression I have tried is similar to :

=sum ({< Date = {"<=$(=date(ShipmentDate),'DD/MM/YYYY') >= $(=date(OrderDate),'DD/MM/YYYY')"} >} OrderAmount)

But as the Date field has always the same value as the OrderDate, the calculation is not correct. Is the data model ready for this calculation? Would the solution be to create new field with all dates?

Many thanks in advance!

Re: Orderbook evolution Chart

marcus_sommer — Mon, 19 Sep 2016 14:31:23 GMT

Maybe something like this (a bit simplyfied):

=sum ({< Date = {"=Date<=ShipmentDate"} >} OrderAmount)

- Marcus

Re: Orderbook evolution Chart

MK_QSL — Mon, 19 Sep 2016 14:36:30 GMT

something like this?

Re: Orderbook evolution Chart

salto — Mon, 19 Sep 2016 15:26:28 GMT

Many thanks Manish, it is helpful but I see two disadvantages: first, the data model is changed (and the table sown is just a part of it and cannot make it as you suggest, unfortunately). Second,if I select a single date the calculation results are affected.

Thanks ofr your help!

Re: Orderbook evolution Chart

salto — Mon, 19 Sep 2016 15:30:19 GMT

Thanks Marcus but it does not give the desired results... Can the problem come from the fact that only orders posted that Date are taken in to account for the calculation?

Kind regards

Re: Orderbook evolution Chart

marcus_sommer — Tue, 20 Sep 2016 06:07:53 GMT

Maybe there is a TOTAL missing within the expression and/or that an interrecord-function like above might useful. If this didn't help please provide a small example with a few inline-data and how should the output look like.

- Marcus

Re: Orderbook evolution Chart

Anil_Babu_Samineni — Tue, 20 Sep 2016 07:46:56 GMT

Hey, You input looking Amount. but, in your expression you have taken OrderAmount.

Can you explain more what does indicated the OrderAmount

Re: Orderbook evolution Chart

MK_QSL — Tue, 20 Sep 2016 07:59:19 GMT

Use this !

Without changing the data model. I have used island table.

Re: Orderbook evolution Chart

antoniotiman — Tue, 20 Sep 2016 08:38:39 GMT

Hi,

maybe

Table:
LOAD Date,
OrderDate,
// ShipmentDate,
Amount
FROM
[https://community.qlik.com/thread/233236]
(html, codepage is 1252, embedded labels, table is @1);

Table1:
LOAD ShipmentDate as Date,
Amount as Amount1
FROM
[https://community.qlik.com/thread/233236]
(html, codepage is 1252, embedded labels, table is @1);

and expression

RangeSum(Above(Sum(Amount)-Sum(Amount1),0,RowNo()))

Regards,

Antonio

Re: Orderbook evolution Chart

vinieme12 — Tue, 20 Sep 2016 08:41:03 GMT

Do you need this is script or straight table?

Cheers

Re: Orderbook evolution Chart

salto — Tue, 20 Sep 2016 09:52:21 GMT

It was a typing mistake, sorry about that.