Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Aggregation and Set Analysis in Pivot Table

Dear all,

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

ClientCurrent BalancePrior BalanceExisting BalanceNew BalanceRepaid
1100150-5000
25000

50

0
320101000
4400004000
5010000

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

1 Solution

Accepted Solutions
Highlighted

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

View solution in original post

4 Replies
Highlighted

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

View solution in original post

Highlighted
Creator
Creator

Thank you very much as always!

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

Highlighted
Contributor
Contributor

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.