Orderbook evolution Chart

[salto]

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!

[marcus_sommer]

Maybe something like this (a bit simplyfied):

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

- Marcus

[MK_QSL]

something like this?

[salto]

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!

[salto]

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

[marcus_sommer]

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

[Anil_Babu_Samineni]

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

Can you explain more what does indicated the OrderAmount

[MK_QSL]

Use this !

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

[antoniotiman · Accepted solution]

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

[vinieme12]

Do you need this is script or straight table?

Cheers

V