BusinessObjects Board

Monthly distinct number since jan 1st

building reports webintelligence
#1

Hello Community

I’m working on a formula to retrieve

  • the distinct number of customers
  • since the beginning of the year
  • recalculated monthly

image
image705×92 2.84 KB

Any idea on what the formula should be?

My query is built with only 2 fields

  • Month number
  • Customer code

I need to build a graph based on this calculation so I can’t do a “cell by cell” calculation…

Thanks a lot for your help

1 Like
#2

This is a more accurate example

The data :
Base

The expected result :

Resultset
Resultset763×89 3.04 KB

I’ve tried to bring the Month numbers from a second query and use a formula like

=Count([Cust];distinct) Where ([Base].[Month number] <= [Month].[Month number])

but then, WebI sends me an “Incompatible objets” error message on the object [Month].[Month number]

#3

Hi,

Have you tried with the runningcount() function ? Without reset dimension it should work fine.
Nb, distinct is the default parameter for counts, I never specify it. When required I specify “all” if I want to count all values.

Regards,
Chris

1 Like
#4

Runningcount doesn’t support the use the all/distinct parameter.

Try something along the lines of:

=RunningSum( Sum( If ( ( [NbCust] ForEach( [NbCust] ; [Month] ) In ( [NbCust] ; [Month] ) = Previous( [NbCust] ForEach ( [NbCust] ; [Month] ) ) ) In ( [NbCust] ; [Month]) ; 0 ; 1 ) ) )

— note this only works if Month is the only dimension in your data block, if you add other dimensions, they will need to be added to the contexts.

Here’s a link to a blog on the subject of distinct running counts (SAP login not required):

https://blogs.sap.com/2015/11/13/how-to-implement-a-running-count-distinct/

1 Like