Exclude Nulls from Maximum date value

[helen_pip]

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

[sunny_talwar · Accepted solution]

Try this:

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

[helen_pip]

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

[helen_pip]

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

[sunny_talwar]

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

[helen_pip]

Hello Sunny

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

Kind Regards

Helen

[sunny_talwar]

Try this

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

[helen_pip]

We aspire to have your knowledge!

Thank you, this works

Thanks

Helen