If Data is Null - Pick next available date

[helen_pip]

Dear Qlikview User

I have a dataset which contains a layout similar to below:

Date                    Metric            Numerator

01/01/2017              A                     20

01/02/2017              B                     -

01/03/2017              C                    -

In some instances there will be null values for a month.

What I want to do is create an expression which will ignore the null values (I can change it to any appropriate symbol) and then pick up the latest numerator

Can anyone kindly point me in the right direction on how I can pick up the maximum numerator which is only populated with a real value

My expression so far is wrong as it is always picking up the Max Date

Num(sum({$<Date={'$(=Max(Date))'}>}

Numerator))

Kind Regards

Helen

[sunny_talwar · Accepted solution]

May be this

Num(Sum({$<Date={"$(=Max({<Numerator = {'*'}>}Date))"}>}Numerator))

[helen_pip]

Hello Sunny

Thank you for your code, I have tried this, but return a 0 value

Would the Alt function help ignore the null values?  In addition what does the * stand for?

Kind Regards

Helen

[MK_QSL]

Or may be..

  SUM({$<Date = {"$(=Date(Max({<Numerator = {'*'}>}Date)))"}>}Numerator)

[helen_pip]

Hello Sunny

My apologies, but want to confirm that your answer is accurate, I needed to add in extra piece of code my end

You answer is correct

Thank you so much!

Helen

[sunny_talwar]

Super... please close the thread by marking correct and helpful responses...

Best,

Sunny