topic Re: cumulative counter on a text dimension in a pivot chart in App Development

cumulative counter on a text dimension in a pivot chart

chrismtb — Fri, 23 Apr 2021 10:47:44 GMT

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

Re: cumulative counter on a text dimension in a pivot chart

rubenmarin — Sat, 24 Apr 2021 07:33:29 GMT

hi, can you try this?:

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

Re: cumulative counter on a text dimension in a pivot chart

chrismtb — Mon, 26 Apr 2021 13:57:09 GMT

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