Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
m_perreault
Contributor 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)
5 Replies
Rodj
Contributor II

Re: Help with Above Function

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
Contributor III

Re: Help with Above Function

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
Contributor II

Re: Help with Above Function

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

m_perreault
Contributor III

Re: Help with Above Function

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
Contributor II

Re: Help with Above Function

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