cumulative counter on a text dimension in a pivot chart

[chrismtb]

Good morning Qlikers,

I have a pivot table with several dimensions (which are grouped in the on screen view). Is it possible to dynamically calculate (in an expression)  how many different values there are in the dimension in a cumulative way?

To try and make that question clearer my data set would look something like this:

Area	Team	Staff Name	Calc (No Hours)	Team Count
North West	Team A	Steve	8	1
North West	Team A	Dave	7	1
North West	Team A	Eric	12	1
North West	Team B	Steph	11	2
North East	Team C	Carrie	10	3
North East	Team C	Mark	7	3
North East	Team C	Pete	8	3
North East	Team D	Chris	4	4

Items with Blue headers are the dimensions.

Items with Purple headers are expressions.

The column in Red is the count... I can't do this via a mapping table in data load as the pivot changes dependent on filters and I need the count to be dynamic.

I have used cumulative sums previously but don't have a clue if / how that could be applied in this situation.

Any ideas would be welcome!

Thanks

[rubenmarin · Accepted solution]

hi, can you try this?:

RangeSum(Above(TOTAL [Team Count]),If(Team=Above(TOTAL Team), 0, 1))

[chrismtb]

Perfect  @rubenmarin .

This does exactly what I needed and adds the cumulative count as an expression.

I have taken it further and referred to this field in my conditional formatting allowing me to add alternate stripes to the table by the Team groupings making my table easier to read!!

 =if(odd(TeamCount)=0,rgb(242,242,242) 

This means I get a table looking like this:

Thanks once again!

Chris