Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
sqlpuzzle
Contributor III
Contributor III

Cumulative Count Distinct Ignoring Certain Dimension Filters

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

sqlpuzzle_0-1606335338429.png

 

With Filters

sqlpuzzle_1-1606335391524.png

Thanks

Labels (2)
1 Solution

Accepted Solutions
Kush
MVP
MVP

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

 

View solution in original post

4 Replies
sqlpuzzle
Contributor III
Contributor III

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.

sqlpuzzle_0-1606341075485.png

 

What can be done.

Kush
MVP
MVP

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

 

View solution in original post

sqlpuzzle
Contributor III
Contributor III

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.

sqlpuzzle
Contributor III
Contributor III

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