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 think one of the brackets is incorrect...

bimanbeginner
Contributor II
Contributor II
Author

I tried like this but its returning 0

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

Anonymous
Not applicable

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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