Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

nathan_parmeswa
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

Capture.PNG

This is what I want it to show.

Capture1.PNG

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

16 Replies
omarbensalem
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:

Capture.PNG

and it works even when you change the sorting:

Capture.PNGCapture.PNG

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

omarbensalem
Esteemed Contributor

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

maybe stalwar1‌ could help us here?

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

omarbensalem
Esteemed Contributor

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

What do u mean by ExpressionLabel sunny?

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

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

Capture.PNG

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

omarbensalem
Esteemed Contributor

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

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