Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aaronnayan
Creator III
Creator 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
dplr-rn
Partner - Master III
Partner - Master III

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)

 

View solution in original post

10 Replies
dplr-rn
Partner - Master III
Partner - Master III

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)

 

dplr-rn
Partner - Master III
Partner - Master III

ideally create master calendars for your date fields
aaronnayan
Creator III
Creator III
Author

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)

 

dplr-rn
Partner - Master III
Partner - Master III

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
Creator III
Creator III
Author

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 ?

 

 

dplr-rn
Partner - Master III
Partner - Master III

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
Creator III
Creator III
Author

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

dplr-rn
Partner - Master III
Partner - Master III


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

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?