topic Re: Re: Summing across a pivot table in QlikView

Summing across a pivot table

Tue, 29 Oct 2013 02:53:06 GMT

Hi All

Is it possible to sum across a pivot table to get an average over 2 months to perform a calculation?

Scenario

Attached pivot table screenshot

the calculation is

sum(stock) for Oct / ((sum sales for Oct) +sum(Sales for Sep) / (count #weeks for Oct) + count(#weeks for Sep))

the Amount in Week for October should be 21 but currently I can only perform the calculation over 1 month which gives me 26

Cheers

Brett

jagan — Tue, 29 Oct 2013 04:11:31 GMT

Hi,

Try Chart Inter Record functions like Below()/Above()/Before()/After() like this

sum(stock) for Oct / ((sum sales for Oct) +sum(Sales for Sep) / (count #weeks for Oct) + count(#weeks for Sep))

=sum(stock)/(RangeSum(sum(stock), Above(sum(stock))) / (RangeSum(Count(Weeks), Above(Count(Weeks)))

If above expression not works then try changing Before() or attach sample file.

Regards,

Jagan.

sushil353 — Tue, 29 Oct 2013 04:20:40 GMT

Hi Brett,

Upload sample application for better resolution.

Sushil

Thu, 31 Oct 2013 03:40:12 GMT

Hi Sushil

See attached document

jagan — Thu, 31 Oct 2013 04:17:44 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Thu, 31 Oct 2013 04:21:49 GMT

Thanks Jagan, works well