16 Replies Latest reply: Feb 13, 2018 5:44 PM by Nathan Parmeswar

# If value is zero, show previous non-zero value

Hello,

I am new to Qlik Sense and I would like to know how to mentioned in the UI layer (not the script) to assign a previous value if a given measure is equal to zero.

In this example, We have date as a dimension and price as a measure, if list price is equal to zero for a given finalized date, we want the price to be the price of the last non-zero price.

This is what is shows right now

This is what I want it to show.

is there any way we can do this for all cases like this?

• ###### Re: If value is zero, show previous non-zero value

Try this:

Aggr(if(Sum(Price)<>0,sum(Price),

if(sum(Price)=0,

if(Above(sum(Price))<>0,above(sum(Price)),

if(above(Above(sum(Price)))<>0,above(above(sum(Measure))),

if(above(above(Above(sum(Price))))<>0,above(above(above(sum(Price)))),

if(above(above(above(above(Above(sum(Price))))))<>0,above(above(above(above(Above(sum(Price))))))

))))))   , Date)

Result per analogy:

and it works even when you change the sorting:

• ###### Re: If value is zero, show previous non-zero value

Thank you Omar. If I understand this code correctly, this would only work if the number of consecutive zeros in the dataset is less than or equal to 5 (5 nested above() functions). Is there any way we can execute something like this if the maximum number of consecutive zeros is unknown in the dataset?

Aggr(if(Sum(Price)<>0,sum(Price),

if(sum(Price)=0,

if(Above(sum(Price))<>0,above(sum(Price)),

if(above(Above(sum(Price)))<>0,above(above(sum(Measure))),

if(above(above(Above(sum(Price))))<>0,above(above(above(sum(Price)))),

if(above(above(above(above(Above(sum(Price))))))<>0,above(above(above(above(Above(sum(Price))))))

))))))   , Date)

• ###### Re: If value is zero, show previous non-zero value

maybe stalwar1 could help us here?

• ###### Re: If value is zero, show previous non-zero value

The only way to do this would be to column reference the same expression

If(Expression = 0, Above(ExpressionLabel), Expression)

But the problem with this is that it may not work in the desired fashion when you make selections.

• ###### Re: If value is zero, show previous non-zero value

What do u mean by ExpressionLabel sunny?

• ###### Re: If value is zero, show previous non-zero value

Would you be able to share the qvf that you have posted the snapshot for? It might be easy to show then to explain?

Alternatively, you can use Column() function

If(Expression = 0, Above(Column(1)), Expression)

• ###### Re: If value is zero, show previous non-zero value

I've tried this:

if(sum(Measure)=0,Above(Column(1)),sum(Measure)) (the last column)

and this was the result:

ps: can't share the file; it contains ALL of my interventions in the community

last intervention;

exit script;

the rest..

and MANY SHEETS...

• ###### Re: If value is zero, show previous non-zero value

So the expression is now Column(3)

if(sum(Measure)=0,Above(Column(3)),sum(Measure))

• ###### Re: If value is zero, show previous non-zero value

That's... perfect !! Never knew such a thing was possible

• ###### Re: If value is zero, show previous non-zero value

It is, but not the ideal way to do it....

• ###### Re: If value is zero, show previous non-zero value

Do we need another column? What if we do all the checking in the Price column itself. Not sure though?

Date, Price

1/02/2014,2000

4/21/2014,23431

6/27/2015,34125

11/02/2017,0

12/31/2017,0

1/10/2017,0

1/12/2017,0

1/13/2017,0

1/16/2018,12343 ];

• ###### Re: If value is zero, show previous non-zero value

We don't ... that is sort of where this all started from... but Omar already had two expressions.... so that is why Column(3).

The basic idea is to self reference the expression for the value it shows on the previous row.

• ###### Re: If value is zero, show previous non-zero value

Yeah, right. hope nathan.parmeswar got options to move forward.

• ###### Re: If value is zero, show previous non-zero value

How to handle this in the script sunny?

• ###### Re: If value is zero, show previous non-zero value

Much more easier to do this in the script

If(Price = 0, Peek('New_Price'), Price) as New_Price

• ###### Re: If value is zero, show previous non-zero value

Thank you Sunny, Omar, and Digvijay. Very helpful.