Calculate the rate for last month compare with this month

[#1]

Hi,

I work at a company that sales merchandies. Each month we have a number of incoming orders and each month we also have a number of drop off:s. The drop off:s connected to a month is the result of drop off made in last month.

The drop off:s should be compared to the last month orders to calculate the drop off rate. See ex. below

Month     Orders          Drop off

jan          1000            500             

feb          1500            750

mar         2000           1000  

apr          2500            200

The calculation for the drop off rate is: drop off in feb. / orders in jan. = drop off rate in jan.

How should I write the calculation for this?

Thanks!

[#2]

For my understanding Drop off for Feb = 750 / (1000 - 500)? Is that right?

Best Regards,
Vishal

[#3]

The drop off for Feb = 750/1000.

The date for orders and drop off is based on the same date: purchase_date. Each row of purchase has orders = 1 and drop_off = 1/0. The numbers you see in the table is the sum of orders and drop_off for each month.

[#4]

Please check if the attached file serves your purpose. Its a technique that has been shown on the community.

Best Regards,
Vishal

[#5]

Hi,

It almost works. Is it possible to create the AsOfMonth and the Type in a script instead of having it in a excel, because we get all our data from the DW.

An other thing: i was wrong before when you were asking about the rate for Feb.. it should be 1000/1500 and placed on the Feb row.

This is how it should look like:

Month     Orders          Drop off     Drop off rate          Calculation

jan          1000            500            0,75                     750/1000 

feb          1500            750            0,66                     1000/1500

mar         2000           1000           0,1                       200/2000

apr          2500            200

Thanks!

[#6]

1. Check this out. Instead of Previous I added Next. Quick look says it works fine.

2. Regarding data load I think (and with the disclaimer that I am new to qlikview )

     - Qlikview allows pulling data from multiple sources.

     - Inline load is an option but if the data is huge then it will be a pain i think.

     - Can you add the table to DW?

3. Let me see if there is another method to get this done that does not require the Monthly Cal.

Best Regards,
Vishal