
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- « Previous Replies
-
- 1
- 2
- Next Replies »

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
maybe stalwar1 could help us here?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What do u mean by ExpressionLabel sunny?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So the expression is now Column(3)
if(sum(Measure)=0,Above(Column(3)),sum(Measure))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That's... perfect !! Never knew such a thing was possible

- « Previous Replies
-
- 1
- 2
- Next Replies »