Qlik Community

Qlik Sense App Development

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

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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)

 

View solution in original post

10 Replies
Highlighted
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)

 

View solution in original post

Highlighted
Partner
Partner

Re: Set Analysis Track Conversion Over time

ideally create master calendars for your date fields
Highlighted
Creator III
Creator 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)

 

Highlighted
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
Highlighted
Creator III
Creator 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 ?

 

 

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

Highlighted
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?
Highlighted
Creator III
Creator 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?