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 get the Error :"Error in expression Nested Aggregation not allowed"

[prat1507]

Yes.

Use your expression as:

$(replace(vMTDCALC ,'<','<FIN_YR_MTH_NO={'&chr(39)&'$(vMaxFinYrMthNo)'&chr(39)&'},'))

[bimanbeginner]

This is returning a null

[bimanbeginner]

Let me explain my requirement maybe im approaching this from the wrong angle.

I have two surveys.

Survey 1 and Survey 2

each survey has 3 ratings,

Poor,Average and Excellent

The overall score must be made up of 30% of Survey1 and 70% of Survey 2

The score is calculated as follows.

Excellent /(Excellent+Average+Poor) * 30%

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

Similarly for Survey2

Excellent /(Excellent+Average+Poor) * 70%

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

Then I need to add them together to get and overall score.

Excellent /(Excellent+Average+Poor) * 30%

+

Excellent /(Excellent+Average+Poor) * 70%

This has worked for me so far, my problem is that I need to create a YTD view on this so that if the user select Mar

they should see the The average over the 3 months Jan %+ Feb %+ Mar %/3

[pradosh_thakur]

why not us the maxdate variable in the first variabel directly with set anlysis .

else try this  and vMTDCALC without = in the defination .

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

[bimanbeginner]

The problem with using the date in the initial variable is that it will sum up the values and then calculate the % on the total. I need to get an average over the 3 months by taking the calculated average and / the no of months

[pradosh_thakur]

did the above expression work ? try playing around with or without equal to and dollar signs .

regards

Pradosh

[sunny_talwar]

May be this

Store variable without =

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)}>}Aggr($(vMTDCALC)), Survey))

[bimanbeginner]

Hi Sunny

.Error on text box, cant seem to see where the problem is Error in set modifier ad hoc element ....

[sunny_talwar]

How about this

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)'}>}Aggr($(vMTDCALC)), Survey))