topic Re: Aggregation and Set Analysis in Pivot Table in QlikView

Aggregation and Set Analysis in Pivot Table

aetingu12 — Wed, 31 Oct 2018 15:00:41 GMT

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

Re: Aggregation and Set Analysis in Pivot Table

sunny_talwar — Wed, 31 Oct 2018 15:07:47 GMT

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

Re: Aggregation and Set Analysis in Pivot Table

aetingu12 — Wed, 31 Oct 2018 15:12:09 GMT

Thank you very much as always!

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

Re: Aggregation and Set Analysis in Pivot Table

sunny_talwar — Wed, 31 Oct 2018 15:25:34 GMT

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

Re: Inputsum Totals Not pulling correct totals

bjjackson2760 — Mon, 01 Apr 2019 14:17:32 GMT

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.