open orders on a given day

[Simon4]

Hi all,

I want a running line graph with open orders on a given day.  how do I create this? I've looked at a few solution sbut can't get it to work. I am using a master calander.

these are my fields:

'iQ|'&"PK_R_ORDER" as OrderID,
'iQ|'&"FK_SALESPERSON" as VerkoperID,
'iQ|'&"FK_ORDERCATEGORY" as OrderCatID,
'iQ|'& FK_MAINPROJECT as ProjectID,
'iQ|'&"FK_RELATION" as RelationID,
'iQ|'& FK_WORKFLOWSTATE as WorkflowID,
'iQ|'&"FK_DEALER" as DealerID,
floor(ORDERDATE) as DateID,

'iQ' as Source,


ORDERNUMBER as OrderNumber,
DESCRIPTION as OrderDesc,
Date(ORDERDATE) as OrderDate,

ORDERTYPE as OrderType,
TOTALNETAMOUNT as OrderIntake,
TOTALCOSTPRICE as OrderKostprijs,
INVOICEDAMOUNT as OrderGefactureerd,
TOTALNETAMOUNT - TOTALCOSTPRICE as OrderMarge,
ISPROJECTMAINORDER,
FINANCIALDONE,
date(DATEFINANCIALFINISHED) as DatumFinancieelGereed,

Openingdate = OrderDate

closingdate = DatumFinancieelGereed

thank you in advance,

Kind regards

[Kushal_Chawda]

@Simon4  could please elaborate?

[Simon4]

Hi @Kushal_Chawda,

I attached some sample data. 

I want to make a line chart that displays how many open orders there were on a given day.

OrderDate is the opening date and the order stays open until it is send OR finacial done, which both have dates aswell. 

I basically need a open order count per day, but I am not able to get it working.

[MarcoWedel · Accepted solution]

Hi,

one solution might be:

tabOrders:
LOAD OrderNumber,  
     Date(OrderDate,'DD-MM-YYYY')				as OrderDate, 
     Date(SendDatepakbon,'DD-MM-YYYY')			as SendDatepakbon,  
     Date(DatumFinancieelGereed,'DD-MM-YYYY')	as DatumFinancieelGereed
FROM [lib://DataFiles/SAMPLE DATA qlik com 2076736#M88305.xlsx] (ooxml, embedded labels, table is Sheet1);

tabDates:
LOAD OrderNumber,
     Date(OrderDate+IterNo()-1,'DD-MM-YYYY') as Date
Resident tabOrders
While OrderDate+IterNo()-1 <= RangeMin(SendDatepakbon,DatumFinancieelGereed,Today());

see also: https://community.qlik.com/t5/Design/Creating-Reference-Dates-for-Intervals/ba-p/1463944

[Simon4]

Thankyou! It works perfectly.

I only had to create a second calander to prevent circle references, but in this case that is not a problem.

Cheers

Simon