topic Re: Averages in pivot table in QlikView

Averages in pivot table

Wed, 26 Mar 2014 10:50:43 GMT

Hi all,

i have a pivot table ant a straight table, on the pivot table i got a wrong averages on total line, on stright table ist calculate ok. I need get the same values (total averages) on pivot as i have on straight table. please help.( on strigt table, in expression tab i can select avg on total mode, but in pivot table this selection is not available.)

my expresions is:

sum({$<Invoice.InvoiceStatus = {'$(vInvoiceApprove)'}>} Invoice.RevenueLT/Currency.Rate)

sum({$<Invoice.InvoiceStatus = {'$(vInvoiceApprove)'}>}Invoice.Tons)

Re: Averages in pivot table

its_anandrjs — Wed, 26 Mar 2014 10:59:47 GMT

Hi,

Write like this

=(sum({$<Invoice.InvoiceStatus = {'$(vInvoiceApprove)'}>} Invoice.RevenueLT) / Currency.Rate)

sum({$<Invoice.InvoiceStatus = {'$(vInvoiceApprove)'}>}Invoice.Tons)

Hope this helps

Thanks & Regards

Re: Averages in pivot table

Wed, 26 Mar 2014 11:04:00 GMT

not working.. i think i need use aggr function, but dont know how...

Re: Averages in pivot table

DavidFoster1 — Wed, 26 Mar 2014 11:04:18 GMT

I would be very careful around averages in QlikView. As you can see it is very easy to get the wrong answer.

The problem appears to be a standard average of averages problem, but your expression 'should' be fine.

For this I would tend to use an aggr statement so that I have more control over the context of the average.

I have not tested this, but try something like:

avg(aggr(

sum({$<Invoice.InvoiceStatus = {'$(vInvoiceApprove)'}>} Invoice.RevenueLT/Currency.Rate)

sum({$<Invoice.InvoiceStatus = {'$(vInvoiceApprove)'}>}Invoice.Tons),Customer,Product))

Re: Averages in pivot table

Wed, 26 Mar 2014 11:13:05 GMT

thanks, works fine.

Re: Averages in pivot table

its_anandrjs — Wed, 26 Mar 2014 11:16:14 GMT

Try like this

Aggr( Avg( sum({$<Invoice.InvoiceStatus = {'$(vInvoiceApprove)'}>} Invoice.RevenueLT/Currency.Rate)

sum({$<Invoice.InvoiceStatus = {'$(vInvoiceApprove)'}>}Invoice.Tons)),Customer)

Hope this helps

Thanks & Regards