Error in an expression to get a value corresponding to the latest(max) date for each dimension value

[karthick_hoag]

hi,

I need your help and expertise to solve an issue in writing an expression.

Here is the data:

METRIC_NAME	EFF_DATE	VALUE_01	VALUE_01_DESC
Access - Wait Times - Urgent Care (Door to Provider)	43891	3	Average
Access - Wait Times - Urgent Care (Door to Provider)	43891	3.4	Average
Access - Wait Times - Urgent Care (Door to Provider)	43977	0.1	Average
Access - Wait Times - Urgent Care (Door to Provider)	43977	0.4	Average
Admin Expense	43891	0	Summation
Admin Expense	43891	64.34	Summation
Admin Expense	43891	138.31	Summation
Admin Expense	43922	-22469.94	Summation
Admin Expense	43922	0	Summation
Admin Expense	43922	55	Summation

Here is my requirement:

I need to get the Avg(VALUE_01) for a metric which has VALUE_01_DESC as Average for the latest Month. Similarly, I need to get the Sum(VALUE_01) for a metric which has VALUE_01_DESC as Summation for the latest Month.

I have a straight table with METRIC_NAME as the dimension and the below expression:

Expression:

=if(VALUE_01_DESC = 'Average',
Avg({<EFF_DATE={">=$(=Max(EFF_DATE))"}>}VALUE_01),

if(VALUE_01_DESC = 'Summation',Sum({<EFF_DATE={">=$(=Max(EFF_DATE))"}>}VALUE_01)
))

I am not getting the expected o/p. Below is what I am getting as a result in the straight table:

METRIC_NAME	Expression
Access - Wait Times - Urgent Care (Door to Provider)	0.25
Admin Expense	0

Expression is not working as expected. I am sensing that the search string within set analysis to find out the max(EFF_DATE) per each METRIC_NAME is not working properly. 

So, how to writ e the search string to get the max(EFF_DATE) corresponding to each dimension (METRIC_NAME) value?

I have to get the below table (Expected O/p):

METRIC_NAME	Expression
Access - Wait Times - Urgent Care (Door to Provider)	0.25
Admin Expense	-22414.94

Thanks,

Karthick S

[jwjackso · Accepted solution]

The Max(EFF_DATE) is 43977, which is for an Average.

=if(VALUE_01_DESC = 'Average',
Avg({<EFF_DATE={">=$(=Max({1<VALUE_01_DESC={'Average'}>}EFF_DATE))"}>}VALUE_01),

if(VALUE_01_DESC = 'Summation',Sum({<EFF_DATE={">=$(=Max({1<VALUE_01_DESC={'Summation'}>}EFF_DATE))"}>}VALUE_01)
))

[karthick_hoag]

Thanks Jwjackso. That worked.