topic Re: pivot not summing up correctly in QlikView

pivot not summing up correctly

arusanah — Fri, 01 Jun 2018 18:12:22 GMT

I have data Coming in as

Charge , service date , Report run date , AMT

1, 1/1/2018, 1/1/2018, 100
1, 1/1/2018, 1/7/2018, 100
1, 1/1/2018, 1/14/2018, 100
1, 1/1/2018, 1/21/2018, 100

I am creating bucket 0-7,7-14,14-21 and so on based on differenc between service date & report date . and expression is sum( amount) .

what is happening is in my Pivot I want to see last report run dates data . i.e amount =100 and bucket should fall in 14-21 bucket instead it shows amount in each bucket . so gives me total outstanding amount as 400

1, 1/1/2018, 1/1/2018, 100 , 0-7
1, 1/1/2018, 1/7/2018, 100, 0-7
1, 1/1/2018, 1/14/2018, 100, 7-14
1, 1/1/2018, 1/21/2018, 100, 14-21

Re: pivot not summing up correctly

sunny_talwar — Fri, 01 Jun 2018 18:58:22 GMT

May be create your bucket like this

Aggr(If([service date] - Max([Report run date]) .....), Charge)

Expression

Sum(Aggr(AMT, Charge))

Re: pivot not summing up correctly

arusanah — Fri, 01 Jun 2018 19:40:44 GMT

with little tweak , I did make above expression work for me however I didn't use use aggr() for Calculated dimensions .

Thanks for your suggestions and Inputs as always .

Hats off to you !