topic Re: (weighted) average ? in QlikView

(weighted) average ?

Fri, 15 Mar 2013 06:17:48 GMT

Hi all.

i am having problem calculating the "average consumption time" of cheques.

One cheque has the following attributes:

- Date of Purchase

- Amount $ (a monetary value)

- Expiration Date

- Consumption Date (if 100% of "Amount $" is spent)

- Status (Active, Consumed or Expired)

By default every cheque has a validation period of 1 year. If the "Amount $" is not completely spent in one year, the cheque expires.

I need to calculate the average time for all cheques from Purchase to Consumed. The challanging part in this is (at least i think so) the fact that the "Amount $" varies from cheque to cheque (I can have a cheque over 100$ and another over 5000$ - obviously the 100$ cheque is likely to be spent faster then the 5000$ cheque...).

How can i consider this fact in my calculation ?

Thanks a lot!

Re: (weighted) average ?

Sun, 17 Mar 2013 16:40:09 GMT

Noone has an idea ?

Re: (weighted) average ?

Sun, 17 Mar 2013 17:11:18 GMT

I wouldn't apply weights to consumption time but would use bands to demonstrate differences for the cheques of different values. e.g.

cheque value band: 0-100; avg consumption time: 5.6 days

cheque value band: 100-500; avg consumption time: 11.2 days

cheque value band: 500-1000; avg consumption time: 14.6 days

cheque value band: 1000-5000; avg consumption time: 23.2 days

cheque value band: 5000+; avg consumption time: 37.4 days

weights are generally applied to make a sample representative of the overall population. in your case, you just want to understand average number of days it takes for the cheques to be spent and how the overall value of the cheque may have an impact on this measure.

another method to understand the dependency of the two data series would be to do a correlation analysis on them. you can do this in excel or using the correl function in qlikview.

Re: (weighted) average ?

Sun, 17 Mar 2013 17:37:01 GMT

Thanks!

And if I would go with the segmentation approach, would I then just do a sum (lifetime) / number of cheques in this segment ?

Thanks a lot!

Re: (weighted) average ?

Mon, 18 Mar 2013 08:48:38 GMT

yes. or if your data is at cheque granularity, you can just simply average the lifetime and slice it by the cheque value band dimension.