Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm going to apologize for this long-winded question in advance, hoping someone can help.
I have a large expression which returns a star value based on a certain threshold ("rate") value determined in the table below:
Measure_ID | STAR1 | STAR2 | STAR3 | STAR4 | STAR5 | PCP_ReportDate |
PartD_MA | 0 | 0.73 | 0.77 | 0.81 | 0.84 | 12/31/2017 |
1. I have an expression I am using to determine the "rate":
=round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
This expression results in .84 for my example.
2. Based on the threshold table, a value of .84 should return a value of "5" in the expression below. However, it is returning a "4" value instead.
= if(num(PCP_ReportDate)> '42947',
if(round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
>= Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR4) and
round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
< Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR5),4,
if(num(PCP_ReportDate)> '42947',
if(round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
>= Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR5),5))))
Is there something I am missing? I've
I appreciate any help you can provide!
STAR expressions needed rounding
=if(num(PCP_ReportDate)> '42947', if(round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
>= Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR1), 0.01) and
round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
< Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR2), 0.01),1,
if(num(PCP_ReportDate)> '42947',
if(round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
>= Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR2), 0.01) and
round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
< Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR3), 0.01),2,
if(num(PCP_ReportDate)> '42947',
if(round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
>= Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR3), 0.01) and
round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
< Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR4), 0.01),3,
if(num(PCP_ReportDate)> '42947',
if(round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
>= Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR4), 0.01) and
round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
< Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR5), 0.01),4,
if(num(PCP_ReportDate)> '42947',
if(round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
>= Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR5), 0.01),5))))))))))
Almost seems like I have looked at this dashboard before . Anyways, would you be able to share a sample... difficult to know what might be going on without playing around with it
I believe you have Now they want to add in rounding from a certain date forward. I think it's still looking at the unrounded number, that's why it's pulling in a 4 instead of 5, but can't figure out how to fix it The expression is "Star" in the table under the chart. Thank you!
STAR expressions needed rounding
=if(num(PCP_ReportDate)> '42947', if(round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
>= Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR1), 0.01) and
round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
< Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR2), 0.01),1,
if(num(PCP_ReportDate)> '42947',
if(round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
>= Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR2), 0.01) and
round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
< Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR3), 0.01),2,
if(num(PCP_ReportDate)> '42947',
if(round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
>= Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR3), 0.01) and
round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
< Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR4), 0.01),3,
if(num(PCP_ReportDate)> '42947',
if(round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
>= Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR4), 0.01) and
round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
< Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR5), 0.01),4,
if(num(PCP_ReportDate)> '42947',
if(round(Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_NUM_STATUS)/Sum({<PCP_Year, PCP_MonthNum = {"=PCP_MonthNum = Max(TOTAL <PCP_Year> Aggr(PCP_MonthNum, PCP_MonthNum, PCP_Year))"}>} PCP_DEN_STATUS),0.01)
>= Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR5), 0.01),5))))))))))
It is working, so thank you so much! But, I guess I'm not understanding why to round those values when they're fixed already in the table?
Looks like an issue related to
I appreciate your help as always!