topic Re: Variable instead of Field in Set Analysis? in QlikView

Variable instead of Field in Set Analysis?

Wed, 04 Jul 2012 16:52:37 GMT

Hiya Guys,

As an example, instead of:

sum( {$<Region = {UK}>} Sales )

(Which will return the sum of the"Sales" field for the current selection, where the “Region” field is "UK").

Is it possible to use a variable instead, like:

sum( {$<Region = {UK}>} vSalesInLocalCurrency )

(Which would return the sum of the calculated variable"vSalesInLocalCurrency" (basically returning the same as "Sales" but as a currency conversion), where the “Region” field is "UK").

As I can't get this to work!

Cheers,

Steve.

PS: This is just an example - my variable is much more complex and so I can't do the caluculation 'afterwards', and I would like to be able to reuse my complex formula.

Re: Variable instead of Field in Set Analysis?

Wed, 04 Jul 2012 17:20:14 GMT

Steve,

Try this,

$(='sum( {$<Region = {UK}>}'&vSalesInLocalCurrency&')')

Regards,

Kiran Rokkam.

Re: Variable instead of Field in Set Analysis?

Thu, 05 Jul 2012 08:55:26 GMT

Hiya Kiran,

Thank you for your response.

Unfortunately this also produces the result "-".

I cannot find any help on the syntax which you've used (above) and so don't entirely understand what you're attempting to do (meaning that I can't think the process through myself!). Would it be possible for you to explain the reasoning behind your (above) formula? (ie: the use of "$" as the first character, the use of single-quotes ('), and the use of ampersands (&)...).

I really appreciate all your help!

Cheers,

Steve.

Re: Variable instead of Field in Set Analysis?

Thu, 05 Jul 2012 09:05:01 GMT

Steve:

We want to make a dynamic expression in the chart. Let me break the steps for this:

1. Create a dynamic expression string (X)

'sum( {$<Region = {UK}>}'&vSalesInLocalCurrency&')'

This step will create the string which we normally type out in the expression editor. Since its not static, we code it with some parameters in this case a variable.

2. Evaluate the string(X)

$(=X)

This would actually do the work once the expression is typed out.

Why all this?:

Well, I dont know a better way.

I hope this clarifies. Some earlier version didnt support this expression and hence we used to do step 1 in a variable and use step to in the expression editor.

Since this doesnt workout for you, can you try it by breaking in two steps? This is one done yesterday

http://community.qlik.com/message/234514#234514

Regards,

Kiran Rokkam.

Sorry missed the last question

Re: Variable instead of Field in Set Analysis?

Thu, 05 Jul 2012 10:07:31 GMT

Thanks Kiran,

Of course, this makes sense now 🙂

I am writing the formula within it's own User Variable - and I am able to evaluate a dynamic expression using a calculated User Variable field, eg:

='Sum({$<Region={UK}>} ' & $(vSalesInLocalCurrency) & ')'

Gives the result (within a Text Object):

Sum({$<Region={UK}>} 1234567890

(Notice the missing close-parenthesis ")" at the end of the text output - ???).

I have noticed that the following formula is evaluated "in-line":

='Sum({$<Region={UK}>} ' & vSalesInLocalCurrency & ')'

(Notice the lack of "$()" around the Variable name).

Which gives text output of:

Sum({$<Region={UK}>} If(Currency='EUR', (Sales * 1.246), Sales)

(Which displays the actual formula).

However, when attempting to evaluate either of these expressions as an actual forumula I get "-" (NULL).

eg:

$(='Sum({$<Region={UK}>} ' & $(vSalesInLocalCurrency) & ')')

...and...

$(='Sum({$<Region={UK}>} ' & vSalesInLocalCurrency & ')')

...both give the result NULL.

Any ideas what I'm missing here?

Cheers,

Steve.

Re: Variable instead of Field in Set Analysis?

Thu, 05 Jul 2012 10:20:10 GMT

Can you change the definition of vSalesInLocalCurrency to

=If(Currency='EUR', 'Sales * 1.246','Sales')

Notice = in beginnering and true/false statements within quotes.

Try using the first expression (with $).

Easeir way to do this is ingore all the previous and have your final expression as:

Sum({$<Region={UK}>} Sales)*if(vSalesInLocalCurrency ='EUR',1.246,1)

Regards,

Kiran Rokkam.

Re: Variable instead of Field in Set Analysis?

Mon, 09 Jul 2012 08:57:24 GMT

Hiya Kiran,

Unfortunately, my variable is much more complicated than the example (above) - and it also uses Set Analysis:

(If(Currency='Local', Sum({$<DateCourse_Year={$(uvCurrentYearMax)}>} M_Forecast),0)+

If(Currency='DKK', Sum({$<DateCourse_Year={$(uvCurrentYearMax)}>} M_Forecast_Dkk),0)+

If(Currency='EUR', Sum({$<DateCourse_Year={$(uvCurrentYearMax)}>} M_Forecast_Euro),0))

I will have a play using your suggestion above - although I am not entirely sure how it would apply to a Set Analysis formula?

Also, this formula may change in the future - thus I would like to reuse this code (ie: within the variable - rather than expanding the formula everywhere it is required). If QlikView cannot make-use of reusing code then this is a very bad design flaw and quite disappointing 😞

I appreciate all your time and help, Kiran, and I will post back with any findings. In the meantime, if you (or anyone else!) has any further ideas please let me know! 🙂

Cheers,

Steve.

Re: Variable instead of Field in Set Analysis?

vincent_ardiet — Mon, 09 Jul 2012 09:20:42 GMT

Hi Steve,

I'm not sure but the fact that you're doing summary at different levels with different set analysis seems not to be good.

Can you try this, create your variable (vSalesInLocalCurrency) with this formula but with no = sign at the beginning, just this text :

{$<DateCourse_Year={$(uvCurrentYearMax)}>} If(Currency='Local',M_Forecast,If(Currency='DKK',M_Forecast_Dkk,M_Forecast_Euro))

Then in your expression :

sum( {$<Region = {UK}>} $(vSalesInLocalCurrency) )

Regards,

Vincent

Re: Variable instead of Field in Set Analysis?

Mon, 09 Jul 2012 16:14:23 GMT

Steve,

This is an usual thing I do. I am not sure why this is not working for you. Before putting it to an expression we need to test the string. For this create a text box with the string and validate the syntax against what is expected for different variable results. If the expression string is right, we will move to the next step.

='Sum({<Region={"UK"}>} '&vSalesInLocalCurrency&')'

Regards,

Kiran Rokkam.

Re: Variable instead of Field in Set Analysis?

Tue, 10 Jul 2012 13:31:15 GMT

Hiya Vincent,

This has worked for me - thank you!

It appears the main point of my problem was that, when using Set Analysis, the formula expects a "field" rather than a calculated number (even though using a static number such as "1234567890" would work!!!). So by creating a User Variable that returned a "field" it was accepted by the Set Analysis formula.

Well, I think that's how it works!

Sorry, Kiran, as mentioned above - I could get the expression to output correctly to a Text Object (minus the closing parenthesis!!! - ???) but any Set Analysis formula would fail when trying to use nested expressions.

Cheers,

Steve.