Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 ways modern analytics can help you take smarter action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MEBG93
Creator
Creator

Hiding values in pivot table, but showing grand total on top

Hello experts, 

Is it possible to hide certain dimensions (and consequent values) but keeping the grand total on the totals row in a Pivot Table?

Let me explain with a brief example:

this first image shows all dimensions

MEBG93_0-1605172117574.png

Next, I want to hide table 2 but maintain the grand total

MEBG93_1-1605172165999.png

Can I do this by associating USERID with the "table" dimension?

Thanks!!

Labels (4)
2 Solutions

Accepted Solutions
MayilVahanan

Hi @MEBG93 

You are using more than one dimension, that's the reason. Above expression not satisfy ur requirement. 
Please try like below

If(IsNull(Rowno(Total)), Sum(Aggr(Sum(Value), Country, [Tier 3])), Sum({<"Tier 3"={'Table 1'}>}Value))

 

Thanks & Regards,
Mayil Vahanan R

View solution in original post

MEBG93
Creator
Creator

Thanks for the replies! I also found a solution using dimensionality(), by defining each dimension to a particular expresion. So for instance, I did:

if(Dimensionality()=1 or Dimensionality()=2,Sum({1<"Tier 4"={'Total'}>}Value),
if(Dimensionality()=4 or Dimensionality()=3, Sum({$<"Tier 3"={'Table 3', 'Table 1'},"Tier 4"-={'Total'} >}Value)))

 

I hope someone find this helpful

View solution in original post

6 Replies
MayilVahanan

Hi @MEBG93 

Try like below

If(RowNo()=0, Sum(Values), Sum({<Table={'table1'}>}Values))

MayilVahanan_0-1605177074766.png

Change the fieldname based on ur data model

Thanks & Regards,
Mayil Vahanan R
MEBG93
Creator
Creator

thanks @MayilVahanan  but it doesn't work. It just sums Values where Table='Table1'.

Also I'm trying this on a pivot table, maybe you're trying on a normal table

MayilVahanan

Hi @MEBG93 

Can you provide the sample app to verify and expected output.

Fyip: screenshot is pivot table..

Thanks & Regards,
Mayil Vahanan R
MEBG93
Creator
Creator

Sure @MayilVahanan i'll send you a sample app. My bad, actually the app has more dimensions and the expression has to be applied to 'Tier 3' (either hide or show a certain value, in this case, show 'Table 1', hide 'Table 2')

What I'm looking for is this highlighted result but in the first column:

MEBG93_0-1605185200143.png

Thanks!

MayilVahanan

Hi @MEBG93 

You are using more than one dimension, that's the reason. Above expression not satisfy ur requirement. 
Please try like below

If(IsNull(Rowno(Total)), Sum(Aggr(Sum(Value), Country, [Tier 3])), Sum({<"Tier 3"={'Table 1'}>}Value))

 

Thanks & Regards,
Mayil Vahanan R

View solution in original post

MEBG93
Creator
Creator

Thanks for the replies! I also found a solution using dimensionality(), by defining each dimension to a particular expresion. So for instance, I did:

if(Dimensionality()=1 or Dimensionality()=2,Sum({1<"Tier 4"={'Total'}>}Value),
if(Dimensionality()=4 or Dimensionality()=3, Sum({$<"Tier 3"={'Table 3', 'Table 1'},"Tier 4"-={'Total'} >}Value)))

 

I hope someone find this helpful

View solution in original post