Averages in pivot table

[#1]

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)

[its_anandrjs]

Hi,

Write like this

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

/

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

Hope this helps

Thanks & Regards

[#3]

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

[DavidFoster1 · Accepted solution]

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))

[#5]

thanks, works fine.

[its_anandrjs]

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