Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Here is the issue I am having. I have a table chart with a count and cumulative count(using the Full Accumulation radio button on the Expressions Tab).
When I have nothing filtered the values are as expected. But as I start filtering the cumulative Count adjusts to the filter by excluding values outside the filter context.
In the two screenshots posted. I want the #CumCount values shown in the screenshot without filters also to show up in the case where filters are selected.
The #CumCount for Jun 2020 should be 19,035 instead of 3,000 all the time.
I want to calculate the cumulative count ignoring some or all of the filter contexts. Is there an expression for that?
With out Filters
With Filters
Thanks
@sqlpuzzle try below expression
=sum(aggr(rangesum(above(Count(distinct {<date_month_year,month_year,year,weekstart>}Id),0,RowNo(TOTAL))),($(=GetCurrentField([YMD])),(NUMERIC,ASCENDING))))
I came to this expression.
RangeSum (above(count({$<year,month_year=>} distinct Id),0,RowNo()))
It seems to get me the cumulative count but it is showing some datarows that I shouldn't bee seeing. The ones outside the filter selection.
What can be done.
@sqlpuzzle try below expression
=sum(aggr(rangesum(above(Count(distinct {<date_month_year,month_year,year,weekstart>}Id),0,RowNo(TOTAL))),($(=GetCurrentField([YMD])),(NUMERIC,ASCENDING))))
This seems to work on the sample. But for some reason I am not getting the result when I add it into my full Project.
It is not feasible to post the full project here. Any hints on where it could go wrong with this expression.
I got your expression to work. My full project had a Calendar Dimension and I had to include the Year, Month, Date from the Calendar and it seems to be fine.
Thanks