topic Re: Set Analysis not returning correct result in QlikView

Set Analysis not returning correct result

byrnel0586 — Fri, 19 Jan 2018 20:35:27 GMT

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!

Re: Set Analysis not returning correct result

sunny_talwar — Fri, 19 Jan 2018 20:43:34 GMT

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

Re: Set Analysis not returning correct result

byrnel0586 — Fri, 19 Jan 2018 20:51:32 GMT

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!

Re: Set Analysis not returning correct result

sunny_talwar — Fri, 19 Jan 2018 22:13:33 GMT

STAR expressions needed rounding

>= 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(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR3), 0.01),2,

if(num(PCP_ReportDate)> '42947',

>= Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR3), 0.01) and

< 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',

>= Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR4), 0.01) and

< 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',

>= Round(Max({<PCP_Year, PCP_MonthNum = {"$(=Max({1<PCP_Year = {$(=Max({1}PCP_Year))}>} PCP_MonthNum))"}>} STAR5), 0.01),5))))))))))

Re: Set Analysis not returning correct result

byrnel0586 — Tue, 23 Jan 2018 14:13:33 GMT

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?

Re: Set Analysis not returning correct result

sunny_talwar — Tue, 23 Jan 2018 14:36:17 GMT

Looks like an issue related to

Rounding Errors

Re: Set Analysis not returning correct result

byrnel0586 — Tue, 23 Jan 2018 14:39:11 GMT

I appreciate your help as always!