topic Re: Count Distinct Based on Sum in QlikView

Count Distinct Based on Sum

Mon, 03 Apr 2017 20:22:51 GMT

I'm fairly new to Qlikview and need some help with a pivot table. I have a dataset that includes Clients; each client can have multiple Segments of business, and each Segment can have multiple Benefits. I have Expenses by fiscal year for each available Benefit. It looks something like this:

I need to show a summary of a subset of clients/expenses based on the Segment and Benefit so I've created a calculated dimension:

=IF((Segment='A' OR Segment='C') AND (Benefit=96 OR Benefit=97 OR Benefit=98),Client)

I'd like to see the sum of expenses for all years, which is easy enough. Here's where I'm stumped: I need to show the count of years where the sum of expenses for the year is less than 0, without having the year as a dimension. I want my final pivot table to look something like this (but obviously with the correct data in the Count column):

f I throw the Year into another pivot, I can use that to eyeball what the results should be, I just don't know how to get it.

Expected Counts:

Client 1 - 2

Client 2 - 3

Client 3 - 1

Client 4 - 3

Client 5 - 0

Any help would be greatly appreciated. I've attached a QVW file if that helps.

Re: Count Distinct Based on Sum

vishsaggi — Mon, 03 Apr 2017 21:54:43 GMT

Check this attached. You cannot use a calculatedDimension in your aggr() expression to get the count. So created a Calculated dimension in the script.

Change your Excel file path if you want to reload the script again.

Re: Count Distinct Based on Sum

craigsutton — Mon, 03 Apr 2017 22:01:23 GMT

=SUM(If((Aggr(Sum(Expense), Client, Year))<0,1))

Re: Count Distinct Based on Sum

sunny_talwar — Tue, 04 Apr 2017 08:31:17 GMT

Another way to avoid the if statement and use set analysis instead of using calculated dimension

=-Sum(Aggr(RangeMin(Sign(Sum({<Segment = {'A'}, Benefit = {96, 97, 98}>+<Segment = {'C'}, Benefit = {96, 97, 98}>}Expense)), 0), Client, Year))

Re: Count Distinct Based on Sum

Tue, 04 Apr 2017 18:02:10 GMT

That works perfectly. Thanks for your help.

Re: Count Distinct Based on Sum

Tue, 04 Apr 2017 18:07:02 GMT

Thanks Craig. Unfortunately I think your formula is picking up some of the Segments and Benefits that are supposed to be excluded so I don't get the correct result in every case:

The answer from @stalwar1 works well by using a set analysis (although I'd prefer to avoid that if at all possible).