5 Replies Latest reply: Apr 4, 2017 2:02 PM by Cory George RSS

    Count Distinct Based on Sum

    Cory George

      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.