topic Re: Variables in QlikView

Variables

hobanwashburne — Wed, 25 Nov 2020 16:16:04 GMT

Why does

x = sum( {< FiscalYear = {2013} >} (NetSales * SalespersonPercentage/100))

y = sum( {< FiscalYear = {2012} >} (NetSales * SalespersonPercentage/100))

sometimes not work, but

x = sum( if(FiscalYear = 2013, (NetSales * SalespersonPercentage/100)),0))

y = sum( if(FiscalYear = 2012, (NetSales * SalespersonPercentage/100)),0))

does as a Variable Definition when used in a Chart Expression as $(x)/$(y)?

Either one work and are equal when used alone

Re: Variables

Anonymous — Thu, 02 Jun 2016 15:40:11 GMT

Hi Scott,

When you have selected FiscalYear 2014, your expression will not work. You use $(x) to evaluate the variable on object.

I think that is better:

x = sum( {< FiscalYear = {'=$(=Max(FiscalYear))'} >} (NetSales * SalespersonPercentage/100))

y = sum( {< FiscalYear = {'=$(=Max(FiscalYear)-1)'} >} (NetSales * SalespersonPercentage/100))

When you add values on Set modifier, is better use simple commas:

x = sum( {< FiscalYear = {'2013'} >} (NetSales * SalespersonPercentage/100))

y = sum( {< FiscalYear = {'2012'} >} (NetSales * SalespersonPercentage/100))

Kind regards.

Re: Variables

hobanwashburne — Thu, 02 Jun 2016 18:08:20 GMT

Thank you for the response, however it is not working in my problem application. I have created a Test application with three identical charts. In the fist I calculate everything in the object, Chart A uses variables and set analysis, Chart B uses variables and an "if statement".

Everything seems to work in the Test application attached, however in the application that I am editing Chart A fails on the % column, it only displays "-".

Also you may notice the field FiscalYear has been changed to YEAR to match the problem application. I thought that because year was also a command it may have been the issue. However, as you can see on the attachment it seems to function in the Test.

Re: Variables

sunny_talwar — Thu, 02 Jun 2016 18:14:43 GMT

I think the issue might be related to the fact that the Year is coming from another table compared to SalesPersonPercentage. I am not 100% sure what the issue is, but set analysis seems to not work under some cases when the data is coming from different tables. I am not 100% sure, but may be swuehl can enlighten us

Re: Variables

swuehl — Thu, 02 Jun 2016 22:55:27 GMT

If you remove the label from your percentage expression and then hover with the mouse of the expression column header, what do you see with the variables expanded? Could you make a screen shot and upload?

Besides this, does your problem application show the exact same data model or is it different?

Re: Variables

hobanwashburne — Fri, 03 Jun 2016 12:26:17 GMT

Thank you, this was incredibly helpful and I believe that I have discovered the problem.

Here is how the original application looked:

Here is what I wanted to change it to (which now functions):

My mistake was rather than replacing the expression I commented out the original expression and added mine below it:

Which resulted in the following failed expression:

Re: Variables

swuehl — Fri, 03 Jun 2016 12:32:26 GMT

Glad that you've got it sorted out.

That's what I've assumed, that your variables expand to something that is not a proper expression.

Comments in variables are likely to mess up expressions that are made of a combination of variables.

I usually only enter a label in the expression header after my expressions are working correctly.

Regards,

Stefan

Re: Variables

sunny_talwar — Fri, 03 Jun 2016 12:35:16 GMT

I usually only enter a label in the expression header after my expressions are working correctly.

Nice advice. I will keep that in mind as well. Thanks for helping Stefan.

Best,

Sunny