topic Re: Exclude Nulls from Maximum date value in QlikView

Exclude Nulls from Maximum date value

helen_pip — Fri, 07 Apr 2017 13:04:29 GMT

Dear Qlikview User

I have a problem, which I am trying to solve and require some expertise with regards to the issue

I have a data set which contains various months and various values, some months contain null values

I.e.

Month Metric Value

01/01/2017 A 2

01/02/2017 A -

01/03/2017 A -

01/01/2017 B 2

01/02/2017 B 6

01/03/2017 B -

I want to sum the value for each metric, but only for the maximum date where data exists

Num(Sum({$<FactDate={"$(=Max({<Numerator = {'>0'}>}FactDate))"}>}Numerator))

The above expression works when both Metrics contain data for the exact same month, but my theory breaks apart when Metric A has data for January and Metric B for February

I am attaching a QVW for your reference but am hoping to achieving the following results for the expression "Latest Month Value"

S004 = 2

S005 = 12

Can anyone help me adjust my expression so I can show the value for the maximum date for each metric

Kind Regards

Helen

Re: Exclude Nulls from Maximum date value

sunny_talwar — Fri, 07 Apr 2017 13:13:38 GMT

Try this:

Num(FirstSortedValue({<Numerator={">0"}>}Aggr(Sum(Numerator), FactDate, [Metric Code]), -Aggr(FactDate, FactDate, [Metric Code])))

Re: Exclude Nulls from Maximum date value

helen_pip — Fri, 07 Apr 2017 13:25:35 GMT

Hello Sunny!

I think I owe you a proportion of wage for the day! I am not consulting, but learning on the job and you have really helped me today

This works

Thanks

Helen

Re: Exclude Nulls from Maximum date value

helen_pip — Fri, 07 Apr 2017 14:10:23 GMT

Hi Sunny

Is it possible from the code you have kindly provided to get the next maximum

I.e.

01/12/2016 = 8

01/01/2017 = 6

01/02/2017 = -

01/03/2017 = -

The code you have kindly provided retrieves data for 01/01/2017, could be altered to retrieve the 8 for December 2016

No worries if not

Kind Regards

Helen

Re: Exclude Nulls from Maximum date value

sunny_talwar — Fri, 07 Apr 2017 14:11:48 GMT

What is the logic to pick Dec 2016? Min Date or Second Max Date?

Re: Exclude Nulls from Maximum date value

helen_pip — Fri, 07 Apr 2017 14:14:00 GMT

Hello Sunny

The logic is that Dec 2016 would be the Second Max date which contains data

Kind Regards

Helen

Re: Exclude Nulls from Maximum date value

sunny_talwar — Fri, 07 Apr 2017 14:15:43 GMT

Try this

Num(FirstSortedValue({<Numerator={">0"}>}Aggr(Sum(Numerator), FactDate, [Metric Code]), -Aggr(FactDate, FactDate, [Metric Code]), 2))

Re: Exclude Nulls from Maximum date value

helen_pip — Fri, 07 Apr 2017 14:17:58 GMT

We aspire to have your knowledge!

Thank you, this works

Thanks

Helen