Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
))
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)
))
Thanks Jwjackso. That worked.