Set Analysis Track Conversion Over time

[aaronnayan]

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]

[dplr-rn · Accepted solution]

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]

ideally create master calendars for your date fields

[aaronnayan]

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]

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]

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]

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]

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]

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

[aaronnayan]

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?