Aggregation and Set Analysis in Pivot Table

[aetingu12]

Dear all,

I am have a pivot table that displays Clients and their Balance Movements as such:

Client	Current Balance	Prior Balance	Existing Balance	New Balance	Repaid
1	100	150	-50	0	0
2	50	0	0	50	0
3	20	10	10	0	0
4	400	0	0	400	0
5	0	100	0	0	100

[Current] = sum({<Date= {"$(vLastMonth)"}>} Balance)

[Prior] = sum({<Date= {"$(v2ndLastMonth)"}>} Balance)

[Existing] = if([Prior]<>0 and [Current]<>0,[Prior]-[Current],0)

[Repaid] = if([Current]=0,[Prior],0)

[New] = if([Prior]=0,[Current],0)

Very simple and straight forward.  I get the totals correctly.

However, I would like to get rid of the Client column a so that I can show just the Balances for all the clients.  When Clients are excluded from the dimension, then the calculations change as the calculation just becomes between the fields and not for each client I have.

How do I run my totals on Clients without having Client as a dimension?

Regards,

Aksel

[sunny_talwar · Accepted solution]

Try this

[Current] = Sum({<Date = {"$(vLastMonth)"}>} Balance)

[Prior] = Sum({<Date = {"$(v2ndLastMonth)"}>} Balance)

[Existing] = Sum(Aggr(If(Sum({<Date = {"$(v2ndLastMonth)"}>} Balance) <> 0 and Sum({<Date = {"$(vLastMonth)"}>} Balance) <> 0, Sum({<Date = {"$(v2ndLastMonth)"}>} Balance) - Sum({<Date = {"$(vLastMonth)"}>} Balance), 0), Client))

[Repaid] = Sum(Aggr(If(Sum({<Date = {"$(vLastMonth)"}>} Balance) = 0, Sum({<Date = {"$(v2ndLastMonth)"}>} Balance), 0), Client))

[New] = Sum(Aggr(If(Sum({<Date = {"$(v2ndLastMonth)"}>} Balance) = 0,Sum({<Date = {"$(vLastMonth)"}>} Balance), 0), Client))

[aetingu12]

Thank you very much as always!

Is there a source I can use to read up on set analysis and aggregations?

[sunny_talwar]

For Aggr

https://www.analyticsvidhya.com/blog/2014/02/aggr/

For Set Analysis

https://ivan-shamaev.ru/wp-content/uploads/2014/08/Set_Analysis_pdf.pdf

[bjjackson2760]

Hi, my totals for my Inputfield are not including the original source totals.  I would like them to include together.  It seems to total on the detail but not the summary totals.  How do I get it to summarize to include the input additions?  Any help you can provide would be greatly appreciate.