Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello can some kindly assist, i've tried numerous ways to determine a running sum based on a count distinct field in a Pivot Table
Pivot rows : Partner Name, RegQtr, Reg Month
Column : Reg Year
Measure : count(Distinct ID) i.e. on the below for Qtr Jan to Mar i need to see:
Jan = 8
Feb = 10
Mar = 12
then needs to reset at next quarter
also i would like an avg column by month within each Qtr i.e.
Jan = 8
feb = 5
Mar = 4
look forward to hearing from you fingers crossed
Hi
not sure but for cumulative sum
may be this :
rangesum(above(
count(Distinct ID)
, 0 , rowno()))
For average
May be this ( but very not sure ) :
rangeavg(above(
count(Distinct ID)
, 0 , rowno()))
another way could be for average to use the first rangesum expression and divide it by a range sum of count distinct month
rangesum(above(
count(Distinct ID)
, 0 , rowno()))
/
rangesum(above(
count(Distinct Month)
, 0 , rowno()))