pivot not summing up correctly

[arusanah]

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

[sunny_talwar]

May be create your bucket like this

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

Expression

Sum(Aggr(AMT, Charge))

[arusanah]

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 !