topic Re: If Data is Null - Pick next available date in QlikView

If Data is Null - Pick next available date

helen_pip — Fri, 07 Apr 2017 09:36:07 GMT

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

Re: If Data is Null - Pick next available date

sunny_talwar — Fri, 07 Apr 2017 09:45:28 GMT

May be this

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

Re: If Data is Null - Pick next available date

helen_pip — Fri, 07 Apr 2017 09:56:54 GMT

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

Re: If Data is Null - Pick next available date

MK_QSL — Fri, 07 Apr 2017 09:58:13 GMT

Or may be..

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

Re: If Data is Null - Pick next available date

helen_pip — Fri, 07 Apr 2017 10:46:05 GMT

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

Re: If Data is Null - Pick next available date

sunny_talwar — Fri, 07 Apr 2017 11:11:10 GMT

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

Best,

Sunny