topic Re: Set Analysis Track Conversion Over time in App Development

Set Analysis Track Conversion Over time

aaronnayan — Sat, 16 Nov 2024 21:06:46 GMT

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]

Re: Set Analysis Track Conversion Over time

dplr-rn — Thu, 04 Apr 2019 19:51:56 GMT

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)

Re: Set Analysis Track Conversion Over time

dplr-rn — Thu, 04 Apr 2019 19:52:24 GMT

ideally create master calendars for your date fields

Re: Set Analysis Track Conversion Over time

aaronnayan — Tue, 09 Apr 2019 09:38:44 GMT

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)

Re: Set Analysis Track Conversion Over time

dplr-rn — Tue, 09 Apr 2019 11:45:06 GMT

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

Re: Set Analysis Track Conversion Over time

aaronnayan — Tue, 09 Apr 2019 12:33:46 GMT

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 ?

Re: Set Analysis Track Conversion Over time

dplr-rn — Tue, 09 Apr 2019 12:44:41 GMT

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

Re: Set Analysis Track Conversion Over time

aaronnayan — Tue, 09 Apr 2019 15:13:31 GMT

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

Re: Set Analysis Track Conversion Over time

dplr-rn — Tue, 09 Apr 2019 15:38:28 GMT

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

Re: Set Analysis Track Conversion Over time

aaronnayan — Tue, 09 Apr 2019 15:41:06 GMT

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?

Re: Set Analysis Track Conversion Over time

dplr-rn — Tue, 09 Apr 2019 15:59:59 GMT

it can but how depends on what you want to achive.

you may need to save your data into a qvd on a daily basis.
read current data - check against saved qvd for changes in some way etc.

overall my first question would be what business benefit is there by capturing the change