Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

bimanbeginner
New 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
MVP
MVP

Re: Set Analysis

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

11 Replies
MVP
MVP

Re: Set Analysis

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

Re: Set Analysis

Hi Massimo

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

MVP
MVP

Re: Set Analysis

bimanbeginner
New Contributor II

Re: Set Analysis

Hi Massimo

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

Regards,

bimanbeginner
New Contributor II

Re: Set Analysis

Hi Massimo

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

MVP
MVP

Re: Set Analysis

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

Re: Set Analysis

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.

MVP
MVP

Re: Set Analysis

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

bimanbeginner
New Contributor II

Re: Set Analysis

Hi Tresesco

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