Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

"-" values appear after a above function is show

I'm doing a very simply pivote table consult, my user doesn't want to see any " 0" or "null" or "-" value, i managed to do so but when i added a new column with an above function in it , the table display a lot of rows with no data in them just "-" !!!! what am i doing wrong???????

5 Replies
johnw
Champion III
Champion III

I assume you're doing something like this:

sum("Sales") + above("Cumulative Sales")

If so, the problem you're seeing originates on the very first row. On the first row, there isn't any row above it. So above("Cumulative Sales") returns null. QlikView's standard is that if you add null to a value, the result is null. So your very first row is null. Since your very first row is null, row 2's above() will return null, and so on, until the entire table is nothing but null values.

There are several solutions, but my favorite is to use the rangesum() function. When you do a rangesum(), null values are treated as zero instead of null. That let's us do the addition we wanted to do. In other words, for the example above, you would use this expression for the Cumulative Sales:

rangesum(sum("Sales"),above("Cumulative Sales"))

If that's not the problem you're having, then I'll need more details.

Not applicable
Author

Thanks a lot ,

i'm on it, i'll let you know

Not applicable
Author

It works!!!! thank you so much!!!, now only one detail, de above function kind of reset the value when my due date change Huh?, I need that it continue the clalculation until the very end!!!!!

any idea here?????

johnw
Champion III
Champion III

The above() function only works within the same "column segment". Basically, it allows you to accumulate data across your FINAL dimension, but resets to null when any OTHER dimension changes. A bad solution would be to replace all but your final dimension with a calculated dimension, like Dimension1 & ' ' & Dimension2 & ' ' & Dimension3. There must be a better solution, though. Sorry I'm not thinking of it. Hopefully someone else will be along with something obvious that I've forgotten.

Shubham_Deshmukh
Specialist
Specialist

Hi @johnw ,

Your reply helped me a lot. Thanks !!!!

-Shubham