topic Re: Averages Per Dimension in QlikView

Averages Per Dimension

Tue, 24 Apr 2012 14:08:46 GMT

Hi all,

please find attached an example of averages I'm trying to calculate.

As you can see, I have two different shipments with different number of invoices.

In the table "Leadtime A-B", I'd like to know the average leadtime of all shipments

1. Average of the lead time between A and B per shipment

E.g. avg leadtime A-B for shipment A =(2+3+5+1+1)/5=12/5

E.g. avg leadtime A-B for shipment B=(1+2)/2=3/2

2. Average of these leadtimes in total

avgleadtime A + avgleadtime B / total number of shipments = ((12/5)+(3/2))/2

If more shipments are selected, then this should be calculated automatically.

I'd like to have it in an expression and over the current selections.

Thanks a lot for your help!

Kind regards,

Marthein

Re: Averages Per Dimension

jagan — Tue, 24 Apr 2012 14:23:00 GMT

Hi,

Hope attached file helps you.

Regards,

Jagan.

Re: Averages Per Dimension

Tue, 24 Apr 2012 14:34:15 GMT

Hi Jagan,

thanks for the quick reply!

I see on total you have an average of 2.43 while I would expect an average of 2.45 (an average of 2.4 and 2.5). This 2.45 is exactly the number I'm after. Can you help me with it?

Thanks,

Marthein

Re: Averages Per Dimension

jagan — Tue, 24 Apr 2012 15:01:30 GMT

Hi,

I think it is not possible, you cannot modify row total, you can do total by expression or sum of rows in straight table.

When you sum Total by expression, it do by (17)/2 This is one option.

The other option is Sum of rows, it just sums up the values. (2.4 + 2.5)

Hope you understand.

Regards,

Jagan.

Re: Averages Per Dimension

Wed, 25 Apr 2012 13:40:54 GMT

Hi,

please find attached another file which shows the issue I'm dealing with.

Basically my source consists of invoices related to shipments, and each shipment has a time "registration" (time A and time B) which is registred at the "invoice" level.

If you look at the file, you see I have an average leadtime (based on the formula provided by you) of 5.43, while the average leadtime per shipment would be 5 (leadtime A + leadtime B divided by 2).

The problem is caused by the fact that shipment A has more invoices, and thus will give more weight to the average equation, while it "should" be independent of the number of invoices in each shipment.

Thanks for your help,

Marthein