# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor

## 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?

16 Replies
Esteemed Contributor

## 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:

New Contributor

## 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)

Esteemed Contributor

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

maybe stalwar1‌ could help us here?

MVP

## 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.

Esteemed Contributor

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

What do u mean by ExpressionLabel sunny?

MVP

## 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)

Esteemed Contributor

## 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...

MVP

## 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))

Esteemed Contributor

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

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