Help with Above Function

[m_perreault]

Hi All,

I have data such as the below

Date	Value
01/01/2020	1
01/02/2020	-
01/03/2020	2
01/04/2020	-
01/05/2020	-
01/06/2020	5

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.  

Date	Value	Formula Output
01/01/2020	1	1
01/02/2020	-	1
01/03/2020	2	2
01/04/2020	-	2
01/05/2020	-	-
01/06/2020	5	5

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

[Rodj]

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]

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]

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

[m_perreault]

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]

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]

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?