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' ORSegment='C') AND (Benefit=96 ORBenefit=97 ORBenefit=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.
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.