topic Excluding dimension values from total row in pivot table in QlikView

Excluding dimension values from total row in pivot table

dafnis14 — Sat, 03 May 2014 12:13:01 GMT

Hi,

I would like to exclude from the total row in a pivot table, values of a certain dimension (family code).

The dimension is flagged in the database for that. Another words, there is a boolean field which indicates whether to include it in the total or not.

But just to check it, I used a specific value (1001).

I wrote the following expression:

=if(RowNo() = 0 or IsNull(RowNo()), Sum({$<[Family Code] ={'<>1001'}>} [Total Available Balance]), sum([Total Available Balance]))

It works fine when there is no selection. The problem is that when a certain family code is selected, the total still shows all values.

Any idea how to resolve it?

Thanks!

Re: Excluding dimension values from total row in pivot table

swuehl — Sat, 03 May 2014 12:35:58 GMT

It might be better if you could upload a small sample application, because without knowing your data model etc., it's not easy to give a correct answer.

Instead of checking for rowno()=0, you could also look into dimensionality() function, but that's probably not causing the issue.

Have you tried excluding the records with the flag like

=if(RowNo() = 0 or IsNull(RowNo()), Sum({$<[Family Code] -= {1001}>} [Total Available Balance]), sum([Total Available Balance]))

Re: Excluding dimension values from total row in pivot table

dafnis14 — Sat, 03 May 2014 13:11:39 GMT

It works!

Thanks!