Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bimanbeginner
Contributor II
Contributor II

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?

Your assistance is appreciated.

32 Replies
bimanbeginner
Contributor II
Contributor II
Author

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

prat1507
Specialist
Specialist

Yes.

Use your expression as:

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

bimanbeginner
Contributor II
Contributor II
Author

This is returning a null

bimanbeginner
Contributor II
Contributor II
Author

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
Master II
Master II

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

Learning never stops.
bimanbeginner
Contributor II
Contributor II
Author

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
Master II
Master II

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

regards

Pradosh

Learning never stops.
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
Contributor II
Contributor II
Author

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