Variable in Set Analysis

[bimanbeginner]

Hi There

I am trying to calculate a percentage out of two formulas, The set analysis doesn't seem to be working with the variable please assist.

vMTDCALC = =


(Sum({<Survey={'Survey1'}>}Excellent)/(Sum({<Survey={'Survey1'}>}Poor)+Sum({<Survey={'Survey1'}>}Average)+Sum({<Survey={'Survey1'}>}Excellent))*0.70

+

Sum({<Survey={'Survey2'}>}Excellent)/(Sum({<Survey={'Survey2'}>}Poor)+Sum({<Survey={'Survey2'}>}Average)+Sum({<Survey={'Survey2'}>}Excellent))*0.30)

In my text box I have

=Sum({<FIN_YR_MTH_NO={$(vMaxFinYrMthNo)}>}$(vMTDCALC))

The Calculation of sum(vMTDCALC) works but it seems to be ignoring my set analysis... it doesn't look at the max year at all, do I need to some how use the Aggrr function?

Your assistance is appreciated.

[bimanbeginner]

I think one of the brackets is incorrect...

[bimanbeginner]

I tried like this but its returning 0

Sum({<FIN_YR_MTH_NO={'$(=vMaxFinYrMthNo)'}>}Aggr($(vMTDCALC), Survey))

[Anonymous]

Based on that description wouldn't that actually be something like

=$(vMTDCALC )/$(vMaxFinYrMthNo)

[sunny_talwar]

How about this

Sum({<FIN_YR_MTH_NO={'$(=$(vMaxFinYrMthNo))'}>}Aggr($(vMTDCALC), Survey))

[bimanbeginner]

When I do it this way, with the = in the variable I get a result but the result it not correct

Sum({<FIN_YR_MTH_NO={'$(=vMaxFinYrMthNo)'}>}Aggr($(vMTDCALC), Survey))

[sunny_talwar]

Try this (as I mentioned above) with and without equal sign

Sum({<FIN_YR_MTH_NO={'$(=$(vMaxFinYrMthNo))'}>}Aggr($(vMTDCALC), Survey))

[bimanbeginner]

When I do the below , I get the correct value for the selection ive made but it seems to ignore the max date in the variable

Avg({<FIN_YR_MTH_NO={'$(=vMaxFinYrMthNo)'}>}Aggr($(vMTDCALC), Survey))

[bimanbeginner]

Without the = it returns 0, with the = its returning an incorrect value same as my above post incorrect value.

Sum({<FIN_YR_MTH_NO={'$(=$(vMaxFinYrMthNo))'}>}Aggr($(vMTDCALC), Survey))

[bimanbeginner]

Hi Sunny

The expression seems to be ignoring the set analysis completely. I have hard coded a Year month and it is still returning whatever is selected.

Avg({<FIN_YR_MTH_NO={'201705'}>}Aggr($(vMTDCALC), Survey))

[sunny_talwar]

How do you create this in the script?

FIN_YR_MTH_NO