Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Suppress zero values AND show total rows where sum = 0

I've a slight problem with a pivot in that I am using the Suppress Zero Values option for the data, but this is removing the total row under certain circumstances.

Quick example to show what I mean;


load * inline


  Name, Amount

  Bob, 2

  Seraphim, -2

  Baldrick, 1


...some nice simple data.

I then create a simple pivot with one dimension, 'Name', and one expression, 'sum(Amount)'.  On the Presentation tab I select to show totals under Name.

My default view on the pivot shows this;

Name      sum(Amount)

Baldrick  1

Bob       2

Seraphim  -2

Total             1

Wonderful so far.  However, when I filter out Baldrick...

Name      sum(Amount)

Bob       2

Seraphim  -2

...no Total row, because it's zero!

Now, in this example it's not much of a problem because even with my brain I can see that -2 and 2 = 0, but in the system I'm developing there can be +ve and -ve transactions which cancel each other out, and there can be LOTS of them, many more than anyone's brain will care to perform a running total on.  There may even be a +5, -3, -1.75, -.25... having that Total line at the bottom showing zero is pretty important to me!

I have kinda faked it, but I don't like the solution, which is to add a new expression column with a value of

    = if ( RowNo() = 0, 'Total', '' )

, so that total lines will have some non-zero data on them.  However, that leaves me with a strange, fake column on my table, and I'd rather not have that there.

So then, the big question:   Is there an option, or a way to implement, 'Suppress Zero Value Rows Except For Total Lines' please?  Or is what I've done about as good as it's going to get?

Thanks for looking.

Tags (1)
1 Reply
Not applicable

Re: Suppress zero values AND show total rows where sum = 0

Slight update - I had to alter my 'fix' so that it was

if ( RowNo() = 0, 'Total', 0 )

...as the 'else' part of an empty string still caused my detail rows to show even if they were all zero.

So now I've got a column at the end of my pivot just showing a long list of zeros.  Groan...