32 Replies Latest reply: Nov 9, 2017 2:44 AM by Rido Trader

# Variable in Set Analysis

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?

• ###### Re: Variable in Set Analysis

Can you try with this

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

• ###### Re: Variable in Set Analysis

Hi Vikas,

This displays an error

• ###### Re: Variable in Set Analysis

Use this

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

Please specify the quotes inside the curly braces.

Regards

Pratyush

• ###### Re: Variable in Set Analysis

Hi Pratyush

The max date variable is being ignored here too...

• ###### Re: Variable in Set Analysis

What is exactly written in your max Month variable?

Regards
Pratyush

• ###### Re: Variable in Set Analysis

=Max(FIN_YR_MTH_NO)

• ###### Re: Variable in Set Analysis

Use =date(Max(FIN_YR_MTH_NO))

Regards
Pratyush

• ###### Re: Variable in Set Analysis

This will break the variable, The current variable is working correctly...

• ###### Re: Variable in Set Analysis

Store your variable  vMTDCALC  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)

• ###### Re: Variable in Set Analysis

I get the Error :"Error in expression Nested Aggregation not allowed"

• ###### Re: Variable in Set Analysis

Yes.

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

• ###### Re: Variable in Set Analysis

This is returning a null

• ###### Re: Variable in Set Analysis

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

• ###### Re: Variable in Set Analysis

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)  & ')'

• ###### Re: Variable in Set Analysis

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

• ###### Re: Variable in Set Analysis

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

regards

• ###### Re: Variable in Set Analysis

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

=\$(vMTDCALC )/\$(vMaxFinYrMthNo)

• ###### Re: Variable in Set Analysis

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))

• ###### Re: Variable in Set Analysis

Hi Sunny

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

• ###### Re: Variable in Set Analysis

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))

• ###### Re: Variable in Set Analysis

I think one of the brackets is incorrect...

• ###### Re: Variable in Set Analysis

I tried like this but its returning 0

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

• ###### Re: Variable in Set Analysis

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

• ###### Re: Variable in Set Analysis

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))

• ###### Re: Variable in Set Analysis

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))

• ###### Re: Variable in Set Analysis

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

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

• ###### Re: Variable in Set Analysis

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))

• ###### Re: Variable in Set Analysis

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))

• ###### Re: Variable in Set Analysis

How do you create this in the script?

FIN_YR_MTH_NO

• ###### Re: Variable in Set Analysis

Hi Sunny,

FIN_YR_MTH_NO this is coming through as is in the data  YYYYMM

• ###### Re: Variable in Set Analysis

Is it a number or date?

• ###### Re: Variable in Set Analysis

Thank You Sunny I managed to resolve it , I Just did the set analysis on the average over the whole expression instead of a variable.