Count Distinct Based on Sum

[#1]

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.

[vishsaggi]

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.

[craigsutton]

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

[sunny_talwar · Accepted solution]

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

[#5]

That works perfectly. Thanks for your help.

[#6]

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