topic open orders on a given day in App Development

open orders on a given day

Simon4 — Fri, 26 May 2023 06:34:51 GMT

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

Re: open orders on a given day

Kushal_Chawda — Fri, 26 May 2023 11:20:18 GMT

@Simon4 could please elaborate?

Re: open orders on a given day

Simon4 — Fri, 26 May 2023 11:28:56 GMT

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.

Re: open orders on a given day

MarcoWedel — Sun, 28 May 2023 17:41:06 GMT

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());

Re: open orders on a given day

Simon4 — Thu, 01 Jun 2023 06:21:21 GMT

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