topic Re: Cumulative Count Distinct Ignoring Certain Dimension Filters in QlikView

Cumulative Count Distinct Ignoring Certain Dimension Filters

sqlpuzzle — Sat, 16 Nov 2024 17:46:32 GMT

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

Re: Cumulative Count Distinct Ignoring Certain Dimension Filters

sqlpuzzle — Wed, 25 Nov 2020 21:51:33 GMT

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.

Re: Cumulative Count Distinct Ignoring Certain Dimension Filters

Kushal_Chawda — Wed, 25 Nov 2020 22:38:57 GMT

@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))))

Re: Cumulative Count Distinct Ignoring Certain Dimension Filters

sqlpuzzle — Mon, 30 Nov 2020 14:58:53 GMT

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.

Re: Cumulative Count Distinct Ignoring Certain Dimension Filters

sqlpuzzle — Mon, 30 Nov 2020 18:34:24 GMT

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