Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
twanqlik
Creator
Creator

Calculating all months in a year per month

I'm not sure how to explain this in words, therefore, i've added a table.

How can i calculate the conversation rate with Qlikview? 🙂 

 jan-18feb-18mrt-18apr-18
Units registered100200150200
Units sold1000100010001000
Conversion10%15%15%16%

 

Conversion:
Jan-18: Sum(Units Registered JAN)\(Units Sold JAN)
Feb-18: Sum(Units Registered JAN + FEB )\(Units Sold JAN + FEB)
Mar-18:Feb-18: Sum(Units Registered JAN + FEB + MAR )\(Units Sold JAN + FEB + MAR)
Apr-18:Feb-18: Sum(Units Registered JAN + FEB + MAR + APR )\(Units Sold JAN + FEB+MAR + APR)

And this for multiple years. Every year follows the same logic

1 Solution

Accepted Solutions
shreya_nadkarni
Partner - Creator
Partner - Creator

Hi,

You can try this.

divide

jan Units_registered/jan units sold for (Jan Conversion) in the expression.(Jan)

Feb Units_registered/Feb units sold (Feb Conversion) and so on. (jan+feb). (attached qvw)

Regards,

Shreya

View solution in original post

4 Replies
shreya_nadkarni
Partner - Creator
Partner - Creator

Hi,

You can try this.

divide

jan Units_registered/jan units sold for (Jan Conversion) in the expression.(Jan)

Feb Units_registered/Feb units sold (Feb Conversion) and so on. (jan+feb). (attached qvw)

Regards,

Shreya

ignacio_pena
Contributor III
Contributor III

Hello,
To do it with an expression you could perform the following steps:

1º I have loaded the indicated data into a table, separating the months and the years as in the following example:

LOAD * INLINE [
year, month, Units registered, Units sold
2017, jan, 500, 1000
2017, feb, 100, 1000
2017, mar, 200, 1000
2018, jan, 100, 1000
2018, feb, 200, 1000
2018, mar, 150, 1000];

2º In a pivot table you have to have the years and months as columns and the expressions as rows (as you indicate in your example).

Captura.PNG

3º The expressions would be the following:

1º expression: sum([Units registered])

2º expression: sum([Units sold])

3º expression (Conversion): 

if(month = 'jan', sum([Units registered])/sum([Units sold]),
if(month= 'feb', (Before(sum([Units registered]))+sum([Units registered]))/(Before(sum([Units sold]))+sum([Units sold])),
if(month= 'mar', (Before(sum([Units registered]), 2)+Before(sum([Units registered]))+sum([Units registered]))/(Before(sum([Units sold]), 2)+Before(sum([Units sold]))+sum([Units sold])), 0)))

For the Conversion I give you an example with 3 months, it would be to extend the "IF" until 12 months. This expression would be valid for all the years you had.

Greetings

ignacio_pena
Contributor III
Contributor III


I forgot to tell you that in the expression of Conversion for each month, you have to add the previous month with the expression of "BEFORE".

For example for April it is:

if(mes= 'apr', (Before(sum([Units registered]), 3)+Before(sum([Units registered]), 2)+Before(sum([Units registered]))+sum([Units registered]))/(Before(sum([Units sold]), 3)+Before(sum([Units sold]), 2)+Before(sum([Units sold]))+sum([Units sold])), ......next expression.

Greetings,
twanqlik
Creator
Creator
Author

Thanks, this seems to work!

I also created a rolling calendar in Excel, and attached it to my normal calendar, that also did the trick.

Jan 2017 - Jan 2017
Feb 2017 - Jan 2017
Feb 2017 - Feb 2017
Mar 2017 - Jan 2017
Mar 2017 - Feb 2017
Mar 2017 - Mar 2017

Etc.