topic Re: Variable in Set Analysis in QlikView

Variable in Set Analysis

bimanbeginner — Fri, 03 Nov 2017 10:27:33 GMT

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.

Re: Variable in Set Analysis

vikasmahajan — Fri, 03 Nov 2017 10:30:11 GMT

Can you try with this

=Sum({<FIN_YR_MTH_NO={$(vMaxFinYrMthNo)}>} (=$(vMTDCALC)) )

Re: Variable in Set Analysis

prat1507 — Fri, 03 Nov 2017 10:41:26 GMT

Use this

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

Please specify the quotes inside the curly braces.

Regards

Pratyush

Re: Variable in Set Analysis

bimanbeginner — Fri, 03 Nov 2017 11:16:58 GMT

Hi Vikas,

This displays an error

Re: Variable in Set Analysis

bimanbeginner — Fri, 03 Nov 2017 11:18:01 GMT

Hi Pratyush

The max date variable is being ignored here too...

Re: Variable in Set Analysis

prat1507 — Fri, 03 Nov 2017 11:19:17 GMT

What is exactly written in your max Month variable?

Regards
Pratyush

Re: Variable in Set Analysis

bimanbeginner — Fri, 03 Nov 2017 11:42:07 GMT

=Max(FIN_YR_MTH_NO)

Re: Variable in Set Analysis

prat1507 — Fri, 03 Nov 2017 11:46:04 GMT

Use =date(Max(FIN_YR_MTH_NO))

Regards
Pratyush

Re: Variable in Set Analysis

bimanbeginner — Fri, 03 Nov 2017 11:50:46 GMT

This will break the variable, The current variable is working correctly...

Re: Variable in Set Analysis

prat1507 — Fri, 03 Nov 2017 11:55:58 GMT

Store your variable vMTDCALC 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)

and then use your expression.

Re: Variable in Set Analysis

bimanbeginner — Fri, 03 Nov 2017 12:02:29 GMT

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

Re: Variable in Set Analysis

prat1507 — Fri, 03 Nov 2017 12:07:44 GMT

Yes.

Use your expression as:

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

Re: Variable in Set Analysis

bimanbeginner — Fri, 03 Nov 2017 12:20:18 GMT

This is returning a null

Re: Variable in Set Analysis

bimanbeginner — Fri, 03 Nov 2017 12:28:44 GMT

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

Re: Variable in Set Analysis

pradosh_thakur — Fri, 03 Nov 2017 12:29:46 GMT

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

Re: Variable in Set Analysis

bimanbeginner — Fri, 03 Nov 2017 12:37:47 GMT

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

Re: Variable in Set Analysis

pradosh_thakur — Fri, 03 Nov 2017 12:42:53 GMT

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

regards

Pradosh

Re: Variable in Set Analysis

sunny_talwar — Fri, 03 Nov 2017 12:55:10 GMT

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

Re: Variable in Set Analysis

bimanbeginner — Fri, 03 Nov 2017 14:24:04 GMT

Hi Sunny

.Error on text box, cant seem to see where the problem is Error in set modifier ad hoc element ....

Re: Variable in Set Analysis

sunny_talwar — Fri, 03 Nov 2017 14:25:28 GMT

How about this

In my text box I have

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