topic Re: Using Functions in Expressions in QlikView

Using Functions in Expressions

Tue, 05 Nov 2013 16:00:56 GMT

Greetings.

I have an expression that I am trying to use a function in. I have percents that need to be summed

=SUM(Percent) works fine. The percentage is a preset amount. (e.g. abc gets 10%, cde gets 90% and both abc and cde fall under xyz which would be at 100%).

This information is repeated on a monthly basis. Over the course of a year xyz winds up with 1200% which is incorrect. xyz should have 100%. If I can get MAX(end_date) into my expression then I will have the number that I need.I am having no luck finding proper syntaxing.

I have =SUM({$<year={"$(=year(MAX(end_date)))"}>}percent)

Am I approaching this incorrectly or is there syntax that I am missing? I have no problem using variables in the expression, but they are not dynamic. I need to use the end_date field value with the YEAR and MAX functions.

Anyone got any ideas?

Re: Using Functions in Expressions

swuehl — Tue, 05 Nov 2013 16:21:34 GMT

If you use

=year(MAX(end_date))

in a text box, what do you get? 2013? And if you use

=SUM({$<year={2013}>} percent)

do you get the correct sum?

Re: Using Functions in Expressions

Tue, 05 Nov 2013 16:28:07 GMT

If "year" field is numeric you can do something like (without cuotes):

SUM({$<year={$(=year(MAX(end_date)))}>}percent)

If you put the expresion "=year(MAX(end_date))" in a variable you can simplify your expresion in this way:

In script:

SET vMaxYear = '=year(MAX(end_date))';

Expresion:

SUM({$<year={$(vMaxYear)}>}percent)

Variable vMaxYear is dynamic relatively of the selections, but is not afected by the dimension of the graphic when you put in a expresion.

What is the dimension of your graphic? You should keep in mind that if Percent is a field precalculated in Load Script time as a Monthly percent when is aggregated by Months in a Qlikview graphic and totalize, monthly value shows fine but the total not.

Re: Using Functions in Expressions

Tue, 05 Nov 2013 16:42:01 GMT

I have tried the text box and it works. I have also tried the expression you wrote and it works. I have percentages from previous years that are not showing up, so I need that dynamic year.