Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
helen_pip
Creator III
Creator III

If Data is Null - Pick next available date

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

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

5 Replies
sunny_talwar

May be this

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

helen_pip
Creator III
Creator III
Author

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

Or may be..

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

helen_pip
Creator III
Creator III
Author

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