Fields values not specific to selection

[byrnel0586]

Hi,

I'm trying to build some calculations in the back end so that I can just use the field names for the values I need on the front end. However, when I use the field names (For instance, Age64_105Num2) I get the value for the entire population even when I have a specific customer selected. But, when I put in the calculation I used in the script I get the customer-specific value. Anyone know how I can fix this?

Here is my script:

PCR_Calc:

LOAD *,

WeightOfAdjProb65_74_2 * AvgAdjProb_65to74_2 as WeightedCal_74_2,
WeightOfAdjProb75_84_2 * AvgAdjProb_75to84_2 as WeightedCal_84_2,
WeightOfAdjProb85_105_2 * AvgAdjProb_85to105_2 as WeightedCal_105_2;


LOAD

_PCR2CalcMoYr as _PCR2Calc_KEY,
Sum(If(AgeFlag=1,NUMERATOR_PCR,0)) as Age64_105Num2,
Sum(If(AgeFlag74=1,AgeFlag74,0))/sum(AgeFlag) as WeightOfAdjProb65_74_2,
Sum(If(AgeFlag84=1,AgeFlag84,0))/sum(AgeFlag) as WeightOfAdjProb75_84_2,
Sum(If(AgeFlag105=1,AgeFlag105,0))/sum(AgeFlag) as WeightOfAdjProb85_105_2,
Sum(If(AgeFlag74=1,[Average Probability of readmission],0))/Sum(AgeFlag74) as AvgAdjProb_65to74_2,
Sum(If(AgeFlag84=1,[Average Probability of readmission],0))/Sum(AgeFlag84) as AvgAdjProb_75to84_2,
Sum(If(AgeFlag105=1,[Average Probability of readmission],0))/Sum(AgeFlag105) as AvgAdjProb_85to105_2


Resident PCR_DETAILS_HEDIS2016

Where (Match (_PCR2CalcMoYr, 'PCR2Feb2017'))
Group By _PCR2CalcMoYr;

[byrnel0586]

I'm pretty sure I know that it is because these calculated fields are static numbers and they need to be variables, but that is what I'm trying to get away from...

[sunny_talwar]

So are you looking to get static value or do you want it to vary? I am confused and not sure I understand?

[byrnel0586]

I need them to vary with each customer selection. I'm thinking I may have to add the calculations to my master table with the Customer IDs??

[sunny_talwar]

May be add CustomerID as your dimension here:

PCR_Calc:
LOAD *,

WeightOfAdjProb65_74_2 * AvgAdjProb_65to74_2 as WeightedCal_74_2,
WeightOfAdjProb75_84_2 * AvgAdjProb_75to84_2 as WeightedCal_84_2,
WeightOfAdjProb85_105_2 * AvgAdjProb_85to105_2 as WeightedCal_105_2;
LOAD
Customer_ID,
_PCR2CalcMoYr as _PCR2Calc_KEY,
Sum(If(AgeFlag=1,NUMERATOR_PCR,0)) as Age64_105Num2,
Sum(If(AgeFlag74=1,AgeFlag74,0))/sum(AgeFlag) as WeightOfAdjProb65_74_2,
Sum(If(AgeFlag84=1,AgeFlag84,0))/sum(AgeFlag) as WeightOfAdjProb75_84_2,
Sum(If(AgeFlag105=1,AgeFlag105,0))/sum(AgeFlag) as WeightOfAdjProb85_105_2,
Sum(If(AgeFlag74=1,[Average Probability of readmission],0))/Sum(AgeFlag74) as AvgAdjProb_65to74_2,
Sum(If(AgeFlag84=1,[Average Probability of readmission],0))/Sum(AgeFlag84) as AvgAdjProb_75to84_2,
Sum(If(AgeFlag105=1,[Average Probability of readmission],0))/Sum(AgeFlag105) as AvgAdjProb_85to105_2

Resident PCR_DETAILS_HEDIS2016

Where (Match (_PCR2CalcMoYr, 'PCR2Feb2017'))
Group By _PCR2CalcMoYr, Customer_ID;

[swuehl]

You have calculated you number for a dimensional context of _PCR2CalcMoYr.

If you need it per customer (but calculated statically in the script), you would need to calculate it per required dimensional context (and maybe for different contexts).

Have a look at

Fact Table with Mixed Granularity

of how you can create a table with facts of mixed granularity (i.e. use several LOADs to  calculate your number for the specific context, and concatenate the resuls into one table. Create a key field  to access the numbers for the specific context you want to retrieve in the frontend).

This can create a huge script and table though.

[byrnel0586]

Unfortunately my Customer_ID field is in another table and not Resident PCR_DETAILS_HEDIS2016

Is there a way to still pull it in?

[byrnel0586]

Thank you, I will check it out!