Set Analysis not returning correct result

[byrnel0586]

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!

[sunny_talwar]

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

[byrnel0586]

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!

[sunny_talwar · Accepted solution]

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))))))))))

[byrnel0586]

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?

[sunny_talwar]

Looks like an issue related to

Rounding Errors

[byrnel0586]

I appreciate your help as always!