Hi,
I'm trying to write an expression to check the order book value (orders confirmed to customers but not yet invoiced). So for each date [Document Date] in a line chart I need to check if the order had received a status of 200 (Order confirmation printed and date exists in [Date Status 200] and if date exists in [Date Status 900] (may be NULL if not yet fully processed). When looking at historical values I then need to check that if the document has an invoice [Date of Invoice], it should not have been invoiced on the date in the chart. The value that I want then want to SUM is the [# Net Amount Order].
All dates are in format YYYY-MM-DD and the values I have to work with are the following (I have other data as well but this is the data from the data model that i think is relevant to be able to write this expression):
[Document Date] (Date, master calendar)
[Document Category] (String, "Order")
[Document Number] (INT, Order number on document)
[Date Status 200] (Date, The date an order was confirmed to customer)
[Date Status 900] (Date, The date when order got the max status where it should still be included)
[Date of Invoice] (Date, When invoice was printed, else NULL)
[# Net Amount Order] (Net value in currency for the total of the order)
I have a current expression that works to check the current order book value (uses other fields than is needed to check historical data since). In this case [Delivery Date] is used instead of invoice date but the "rule" is that invoice is printed on delivery date. The expression I have that works to check the current order book value based on current state is:
sum({<[Delivery date]={">=$(=num(Today()))"}, [Document Category]={"Order"}, [Document Status]={">=200"}, [Document Status]={"<=900"}>}[# Net Amount Order])/1000
However the [Document Status] field only contains the value for the current status of the document so it can only be used to check the current value, not historical values as it changes as the order progresses through the system. Now I need to adapt it to work with historical data and in my data model I load the history of the dates when documents received a status change since that is the only way to use the historical data. I need to use the fields where I have the dates to check when documents where at certain points in the ERP system to decide if document should be included in the order book value for a date or not.
My first attempt for an expression in the line chart to have the orderbook value over time was this (that I am aware does not work which is why I think I will need to use set analysis to create a working solution):
SUM(IF
([Date Status 900] <= [Document Date] AND
[Date Status 200] >= [Document Date] AND
[Date of Invoice] < [Document Date] OR
IsNull([Date of Invoice]),
[# Net Amount Order]/1000))
This the first question I'm posting here in the Qlik community so if I have missed something please let me know and I will add it to the thread.
