Skip to Content
0
Sep 19, 2019 at 10:32 PM

RunningSum Crosstab Distinct Counts

53 Views Last edit Sep 20, 2019 at 10:57 AM 2 rev

Hi Gurus,

Facing an uphill task in doing the RunningSum on Distinct Units in Years. It is a cross-tab report.

I am looking for Running Count of the Distinct Units as we count them. There is a unique unit number in each year. I would like to know how to compare with the Unit number of the following year and doesn't count the unit number which was already counted by previous years.

I am getting cummulative total but not cumulative total of distinct units.

Using following formula

RunningSum(Sum(If (([Unit Number] ForEach ([Unit Number];[Year #]) In ([Unit Number];[Year #]) = Previous([Unit Number] ForEach ([Unit Number];[Year #])) In ([Unit Number];[Year #])) In ([Unit Number];[Year #];[Division]);0;1));Col;([Division]))

Year 1 have 214 units, Year 2 have 224 Units but when you count distinct between these two years then only 236, which is what I am looking for.

What I am looking for

Year 1 Year 2 Year 3

Div 01 214 236 250

Div 02 214 236 250

Div 03 214 236 250

What I am getting

Year 1 Year 2 Year 3

Div 01 214 438 646

Div 02 214 438 646

Div 03 214 438 646

Any help is greatly appreciated.

Thanks