topic Re: Set Analysis in QlikView

Set Analysis

bimanbeginner — Sat, 04 Nov 2017 12:50:28 GMT

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,

Re: Set Analysis

maxgro — Sat, 04 Nov 2017 13:15:30 GMT

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

Re: Set Analysis

bimanbeginner — Sat, 04 Nov 2017 13:43:47 GMT

Hi Massimo

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

Re: Set Analysis

maxgro — Sat, 04 Nov 2017 15:01:53 GMT

could you post a small example?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Set Analysis

bimanbeginner — Mon, 06 Nov 2017 05:37:34 GMT

Hi Massimo

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

Regards,

Re: Set Analysis

bimanbeginner — Mon, 06 Nov 2017 05:52:45 GMT

Hi Massimo

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

Re: Set Analysis

tresB — Mon, 06 Nov 2017 05:56:38 GMT

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

Re: Set Analysis

bimanbeginner — Mon, 06 Nov 2017 06:19:02 GMT

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.

Re: Set Analysis

tresB — Mon, 06 Nov 2017 06:26:22 GMT

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

Re: Set Analysis

bimanbeginner — Mon, 06 Nov 2017 07:21:45 GMT

Hi Tresesco

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

Re: Set Analysis

tresB — Mon, 06 Nov 2017 08:34:22 GMT

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

Re: Set Analysis

bimanbeginner — Mon, 06 Nov 2017 08:50:23 GMT

Thank you so much, Exactly what I needed!!!