Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this:
Num(FirstSortedValue({<Numerator={">0"}>}Aggr(Sum(Numerator), FactDate, [Metric Code]), -Aggr(FactDate, FactDate, [Metric Code])))
Try this:
Num(FirstSortedValue({<Numerator={">0"}>}Aggr(Sum(Numerator), FactDate, [Metric Code]), -Aggr(FactDate, FactDate, [Metric Code])))
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
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
What is the logic to pick Dec 2016? Min Date or Second Max Date?
Hello Sunny
The logic is that Dec 2016 would be the Second Max date which contains data
Kind Regards
Helen
Try this
Num(FirstSortedValue({<Numerator={">0"}>}Aggr(Sum(Numerator), FactDate, [Metric Code]), -Aggr(FactDate, FactDate, [Metric Code]), 2))
We aspire to have your knowledge!
Thank you, this works
Thanks
Helen