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

Set Analysis

Hi

Set Analysis doesn't seem  to work  when summing a variable.

I have hard coded the FIN_YR_MTH_NO and it does not seem to work, any suggestions would be appreciated.

=Avg({<FIN_YR_NO={$(vMaxFinYr)},FIN_YR_MTH_NO={201705},MTH_SHORT_DESC=>}  $(vCALC))

Regards,

1 Solution

Accepted Solutions
tresesco
MVP
MVP

May be this?

= Avg( {<YRMTHNO={"<=$(=(vMaxYearNo))"}>} Aggr(

((Sum({<YRMTHNO={"<=$(=(vMaxYearNo))"}>}GreatScore1)/  (Sum({<YRMTHNO={"<=$(=(vMaxYearNo))"}>}GreatScore1) + Sum({<YRMTHNO={"<=$(=(vMaxYearNo))"}>}AverageScore1) + Sum({<YRMTHNO={"<=$(=(vMaxYearNo))"}>}PoorScore1) ))*0.7)

+

((Sum({<YRMTHNO={"<=$(=(vMaxYearNo))"}>}GreatScore2)/  (Sum({<YRMTHNO={"<=$(=(vMaxYearNo))"}>}GreatScore2) + Sum({<YRMTHNO={"<=$(=(vMaxYearNo))"}>}AverageScore2) + Sum({<YRMTHNO={"<=$(=(vMaxYearNo))"}>}PoorScore2) ))*0.3)

,YRMTHNO))

View solution in original post

11 Replies
maxgro
MVP
MVP

maybe the problem is in FIN_YR_MTH_NO or vCALC

start simple and check if it works, ie (replace Field with one of your field)

=Avg({<FIN_YR_NO={2017},  MTH_SHORT_DESC=>} Field)


and if it works, add the filter for the field  FIN_YR_MTH_NO

bimanbeginner
Contributor II
Contributor II
Author

Hi Massimo

I have tested both variables as suggested , it just seems to ignore the set analysis

bimanbeginner
Contributor II
Contributor II
Author

Hi Massimo

Will do, I will recreate my scenario in a sample model.

Regards,

bimanbeginner
Contributor II
Contributor II
Author

Hi Massimo

Please see my example of what I am trying to do.

tresesco
MVP
MVP

You have to include the same set analysis in the aggregation functions that are in variable as well, like:

vSales=Sum({<Country={'South Africa'}>}[Sales Value])

bimanbeginner
Contributor II
Contributor II
Author

Is there no way of doing the set analysis on the variable?

I am trying to take a percentage of two scores add them together and then get the average of that.

Score1: 80%  Score2: 60%

Calc: (70% of Score1= 56%) + (30% of Score2=18%)

OverallScore=74%

This overall score for each month would be correct.

When I apply the set analysis on the above expression to roll up a YTD value the YTD value does not balance, Im assuming its because of the 70% 30% split I am doing before rolling up my YTD value

Any Idea how I could get the YTD value to roll up balancing to my monthly values.

I will try and post an example to make more sense.

tresesco
MVP
MVP

Could you mimic the requirement in the sample app (may be with a little more data)?

bimanbeginner
Contributor II
Contributor II
Author

Hi Tresesco

Please have a look at the file I have attached I think it explains my problem nicely