Custom Total in Straight Table

[Deputy_Dawg]

Hi Everyone,

I've combed these forums for an answer and I think I got tantalisingly close but no bananna. I'm following privacy guidelines on hospital data and attempting to suppress values in a Qlik App.
That's not so hard, I use the following expression and it works fine:

if(Count(EPSD_ID)<10,'*****',Count(EPSD_ID)) 

However, when you drill into the data, the totals give the suppressed value away.

I've tried if(RowNo()=0 but just couldn't get it to work but I think it might be the best bet.

I thought if(Dimensionality()=0  might get me close but its for pivot tables and not straight tables (I think).

I thought RangeSum might also work but couldn't figure it out.

I wanted to use set analysis for its efficiency with the 100s of millions of rows I've got but couldn't get past not being able to use nested aggregation (the sum of a count). 

So I'm stuck and appeal to you good folk for guidance. I'm sure there must be a simple solution that I am blind to.  Many thanks.

[Chanty4u]

Try this 

if(Sum({<EPSD_ID = {"=Count(EPSD_ID) < 10"}>} Aggr(Count(EPSD_ID), EPSD_ID)) < 10, '*****', Sum(Aggr(Count(EPSD_ID), EPSD_ID)))

Or 

if(Count({<EPSD_ID = {"=Count(EPSD_ID) < 10"}>} EPSD_ID) < 10, '*****', Count(EPSD_ID))

[Deputy_Dawg]

Thanks Chanty, both produced the expected outcome but the Totals is still ignoring the ***** label and still aggregating all the values. Maybe a RowNo()=0 expression would work ??

[MayilVahanan]

Yes, for change the Total Values, you can use if condition like

If(Rowno() = 0 or isnull(Rowno()), urtotalExp, orgExp)

isnull(Rowno()--- for pivot table

[Deputy_Dawg]

I tried both of the following in Presentation/Totals/Totals Label but it didn't change the outcome.

 =if(RowNo()=0, if(Count({<EPSD_ID = {"=Count(EPSD_ID) < 10"}>} EPSD_ID) < 10, '*****', Count(EPSD_ID)))

=if(RowNo()=0, if(Sum({<EPSD_ID = {"=Count(EPSD_ID) < 10"}>} Aggr(Count(EPSD_ID), EPSD_ID)) < 10,

'*****', Sum(Aggr(Count(EPSD_ID), EPSD_ID))))

They are both assessed as OK but still include the suppressed values in the Total.  : (