Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I get the Error :"Error in expression Nested Aggregation not allowed"
Yes.
Use your expression as:
$(replace(vMTDCALC ,'<','<FIN_YR_MTH_NO={'&chr(39)&'$(vMaxFinYrMthNo)'&chr(39)&'},'))
This is returning a null
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
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) & ')'
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
did the above expression work ? try playing around with or without equal to and dollar signs .
regards
Pradosh
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))
Hi Sunny
.Error on text box, cant seem to see where the problem is Error in set modifier ad hoc element ....
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))