topic Re: Using a variable as dimension in set analysis in QlikView

Using a variable as dimension in set analysis

Anonymous — Mon, 15 Aug 2016 15:36:06 GMT

Hello,

I have a flag that I've created using a variable, which is calculated using other variables. I'd like to use this flag variable in my set analysis to get a count of metrics that are 'Good' but I'm having trouble getting it to work. Below are the variables I'm using to create the flag.

vFlag =

if(FACT.TARGET_VARIANCE = 'U',

if($(vActuals)<FACT.THRESHOLD_TARGET, 'Bad',

if($(vActuals)>= FACT.THRESHOLD_TARGET and $(vActuals)< $(vTarget), 'OK', 'Good')),

if($(vActuals)<FACT.TARGET, 'Good',

if($(vActuals)<= FACT.THRESHOLD_TARGET and $(vActuals)> $(vTarget), 'OK', 'Bad')))

vActuals =

if(FACT.MASK_DISPLAY = '#.##%', num(sum(FACT.NUMERATOR)/sum(FACT.DENOMINATOR), '#,##0.00%'),

Median(FACT.MEDIAN))

vTarget =

if(FACT.MASK_DISPLAY = '#.##%', num(FACT.TARGET, '0.00%'),

FACT.TARGET)

What I'm trying to get my set analysis to do is say

count({<$(vFlag) = {'Bad'}>} METRIC.METRIC_DESCRIPTION) but I can't get it to work. I've tried calculating the variables in my script, but that's not working either.

Would anyone have any suggetions?

Thank you in advance.

Re: Using a variable as dimension in set analysis

sunny_talwar — Mon, 15 Aug 2016 15:40:07 GMT

Variables that contain expressions cannot work on the left hand side of the set expression. You might be able to use Aggr() or search string within set analysis to make this to work.

Re: Using a variable as dimension in set analysis

Anonymous — Mon, 15 Aug 2016 15:45:55 GMT

Thank you for the reply Sunny!

I'm a bit confused with your solution. Can you elaborate on what you mean by using Aggr() or a search string in set analysis using the flag?

Re: Using a variable as dimension in set analysis

effinty2112 — Mon, 15 Aug 2016 15:58:29 GMT

Hi David,

I haven't a solution for you but this is a simplification to your clause since if

$(vActuals)<FACT.THRESHOLD_TARGET is false then $(vActuals)>= FACT.THRESHOLD_TARGET is true and you don't need to test it's truth.

vFlag =

if(FACT.TARGET_VARIANCE = 'U',

if($(vActuals)<FACT.THRESHOLD_TARGET, 'Bad',

if($(vActuals)>= FACT.THRESHOLD_TARGET and $(vActuals)< $(vTarget), 'OK', 'Good')),

if($(vActuals)<FACT.TARGET, 'Good',

if($(vActuals)<= FACT.THRESHOLD_TARGET and $(vActuals)> $(vTarget), 'OK', 'Bad')))

It might make it a little easier to work on.

Cheers

Andrew

Re: Using a variable as dimension in set analysis

Anonymous — Mon, 15 Aug 2016 16:07:01 GMT

Set modifiers cannot be any expression,

It should be a field name.

If you can post a sample, we can look into other solutions ...

Re: Using a variable as dimension in set analysis

Anonymous — Thu, 10 May 2018 08:19:07 GMT

Hello David, Try converting the output to text. May help.

vFlag = Text(

if(FACT.TARGET_VARIANCE = 'U',

if($(vActuals)<FACT.THRESHOLD_TARGET, 'Bad',

if($(vActuals)>= FACT.THRESHOLD_TARGET and $(vActuals)< $(vTarget), 'OK', 'Good')),

if($(vActuals)<FACT.TARGET, 'Good',

if($(vActuals)<= FACT.THRESHOLD_TARGET and $(vActuals)> $(vTarget), 'OK', 'Bad')))

)

And then

count({<$(vFlag) = {'Bad'}>} METRIC.METRIC_DESCRIPTION)

Hope this works!Please update us

Thank you