If value is zero, show previous non-zero value

[Anonymous]

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?

[OmarBenSalem]

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:

[Anonymous]

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)

[OmarBenSalem]

maybe stalwar1‌ could help us here?

[sunny_talwar]

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.

[OmarBenSalem]

What do u mean by ExpressionLabel sunny?

[sunny_talwar]

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)

[OmarBenSalem]

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

[sunny_talwar]

So the expression is now Column(3)

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

[OmarBenSalem]

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