Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
m_perreault
Creator III
Creator III

Help with Above Function

Hi All,

I have data such as the below

 

DateValue
01/01/20201
01/02/2020-
01/03/20202
01/04/2020-
01/05/2020-
01/06/20205

 

I want to create a formula so that in the event of a Null for Value such as on 01/02/2020 I take the previous Value.

I created the formula

If(Count(Value)=0,Above(Total Only(Value)),Sum(Value)) which yields the below and works until 01/05/2020 in which the output is null because there was no value on 01/04/2020.  

DateValueFormula Output
01/01/202011
01/02/2020-1
01/03/202022
01/04/2020-2
01/05/2020--
01/06/202055

 

I was wondering if anyone had any ideas on a formula that would accomplish this.  I know I could accomplish this in the script using a Peek function but I have a need to accomplish it via a formula.

@sunny_talwar  was wondering if you maybe had an idea.

 

Thanks in advance!!

Labels (1)
7 Replies
Rodj
Luminary Alumni
Luminary Alumni

Hi @m_perreault ,

I can't think of a way to do this, you can use the self-referencing (recursive) ability of a measure to refer to itself but you still need to be able to dynamically calculate the range of empty values. In any case I'm curious as to what your use-case is. As you say it is easily solved in the load script, I can't think why you would need to dynamically create a value where one doesn't exist that isn't an aggregate of some kind? It would be good to know more of the context of what you are trying to achieve as there may be another way around the problem.

Cheers,

Rod

m_perreault
Creator III
Creator III
Author

The use case is to be able to see the latest entry in the “Value” field based on the Date field. So on 1/2/2020, the latest entry was 1 so show 1 in the Formula Output column.  Ultimately, a table needs to be created that would have multiple columns that would show the latest entry for several different fields.
Rodj
Luminary Alumni
Luminary Alumni

So why can't you do it in the load script?

m_perreault
Creator III
Creator III
Author

As I mentioned, I know I am able to do it in the load script.

 

Because of circumstances my project I am not able to update the load script so I am looking to see if there any ways to do this via a front end formula.

Rodj
Luminary Alumni
Luminary Alumni

Sounds like it might be time to bribe your Qlik Sense admin into giving you access to the load script 🙂

sunny_talwar

You can try this... but this will only work when you have not made any selections

If(Sum(Value) = 0, Above(Column(1)), Sum(Value))
michael_klix
Creator II
Creator II

does this mean that the above() function has problems in a table, if filters are applied?

I have an "above()-Problem" as well,

https://community.qlik.com/t5/App-Development/above-use-in-a-recursive-way-in-a-diagram-NOT-script/t...

maybe you can have a look here, too?