Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
aaronnayan
Contributor III

Set Analysis Track Conversion Over time

Hi Guys,

I'm hoping you can help me with the best way to approach this.

If I have at the start of the month £100,000 of Apples orders at the start of the month. I want to calculate the conversion rate of how many orders were finalised at the end of the month.

 

100 [Open] Orders @ start of month 

50 [Closed] Orders @ end of month 

Conversion Rate should be 50% - How would i calculate this?

However the orders status field for every order "Open", "Closed" can change over time depending on Sales Orders. How would i capture the status of the order at a certain time?

 

Key Fields -

[Order Status]

[Order Create Date]

[Order Close Date]

1 Solution

Accepted Solutions
Partner
Partner

Re: Set Analysis Track Conversion Over time

First things add month year to your data 

e.g. 

load

[Order Status]

,[Order Create Date]
,date(monthstart([Order Create Date]), 'MMM-YYYY') as [Order Create MonthYear]

,[Order Close Date]
,date(monthstart([Order Close Date]), 'MMM-YYYY') as [Order Close MonthYear]

from YOURSOURCE

 

then 

use set analysis

e.g.

Open Orders @ start of current month  (for april 2019 find records with create month year before apr-2019)

Count(  {< [Order Create MonthYear]={"<$(=date(monthstart(today()), 'MMM-YYYY'))"}  >}
           OrderID)

 

Closed Orders current month   (for april 2019 find records with close month year as apr-2019)

Count(  {< [Order Close MonthYear]={$(=date(monthstart(today()), 'MMM-YYYY'))}  >}
           OrderID)

 

10 Replies
Partner
Partner

Re: Set Analysis Track Conversion Over time

First things add month year to your data 

e.g. 

load

[Order Status]

,[Order Create Date]
,date(monthstart([Order Create Date]), 'MMM-YYYY') as [Order Create MonthYear]

,[Order Close Date]
,date(monthstart([Order Close Date]), 'MMM-YYYY') as [Order Close MonthYear]

from YOURSOURCE

 

then 

use set analysis

e.g.

Open Orders @ start of current month  (for april 2019 find records with create month year before apr-2019)

Count(  {< [Order Create MonthYear]={"<$(=date(monthstart(today()), 'MMM-YYYY'))"}  >}
           OrderID)

 

Closed Orders current month   (for april 2019 find records with close month year as apr-2019)

Count(  {< [Order Close MonthYear]={$(=date(monthstart(today()), 'MMM-YYYY'))}  >}
           OrderID)

 

Partner
Partner

Re: Set Analysis Track Conversion Over time

ideally create master calendars for your date fields
aaronnayan
Contributor III

Re: Set Analysis Track Conversion Over time

Hi Dilpranjith,

 

Thanks for your help on this.

For open orders, how would i count all orders up to a specific date i.e 04/04/2019? instead of before April?

Count(  {< [Order Create MonthYear]={"<$(=date(monthstart(today()), 'MMM-YYYY'))"}  >}
           OrderID)

 

Partner
Partner

Re: Set Analysis Track Conversion Over time

Is the 4/4/2019 static?
If it is Use your original date column and use makedate function in same format as above
E g
Order date={"<=$(=makedate (2019,4,4))"}

Typing on phone double check the open and close of braces and quotes
aaronnayan
Contributor III

Re: Set Analysis Track Conversion Over time

Hi Dilipranjith,

 

Yes your right it would be better if it is dynamic. Is there a way to say when we are in April its 04/04/19 and then when we are in May it will be 04/05/19 and then in June 04/06/19 ?

 

 

Partner
Partner

Re: Set Analysis Track Conversion Over time

Sure.. put you logic for date into a variable and use it
e.g. vDateLogc = MakeDate(Year(today()), Month(today()),4)

then similar syntax but substitute the variable instead of the fucntion
Order date={"<=$(=vDateLogc)"}

mark as answered and close thread if it solves the issue
aaronnayan
Contributor III

Re: Set Analysis Track Conversion Over time

Hi Dilpranjth,

 

Thank you that was really helpful. But I am still not there  to the desired result.. I want to track the changes if possible?

Order Won Month can be changed at any time.. Is there a way to capture all orders that have order won month has been changed?

i.e 

Order 1 - [Won month] = April 2019

Order 2 - [Won month] = April 2019

Order 2 changes its won month to May 2019. How would I capture the change?

 

Thanks,
Aaron

Partner
Partner

Re: Set Analysis Track Conversion Over time


thats more of a question on the data / business side
how is that change recorded on the database?
aaronnayan
Contributor III

Re: Set Analysis Track Conversion Over time

It is just overrided via the CRM system..

Is there anyway QV can capture a status in point in time and if it changes from what it previously is?