Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Use set expression in load editor

Hello,

I use following expression in my app

Count( {$<[date.autoCalendar.Year]={$(=max(Year(date)))}>}distinct ID)

and it works fine, but I would like to define the set expression part in data load editor, because I reuse it many times and I want to export it easily.

I tried

Set

MaxYear= '[date.autoCalendar.Year]={$(=max(Year(date)))}'
in load editor and then use

Count( {$<$(=MaxYear)>}distinct ID)

but it does not work. The problem is with the inner function, which is not evaluated. Can anyone fix this problem?

Combination

Set

MaxYear='[date.autoCalendar.Year]={2015}'

and then

Count( {$<$(=MaxYear)>}distinct ID)

works fine, but I need to dynamically evaluate max(Year(date))

Thank you in advance!

Petra

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The variable created in the SET will be incorrect because the $() will get substituted during the script run. You should be able to see this problem by looking at the variable in the variable overview.  To avoid the $() substitution in the script you have to use one of these trick techniques.

LET MaxYear= '[date.autoCalendar.Year]={$' & '(=max(Year(date)))}';


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

View solution in original post

7 Replies
dwforest
Specialist II
Specialist II

Something like:

Set MaxYear= '[date.autoCalendar.Year]={' & max(Year(date)) & '}'


Use the debug feature to make sure MaxYear evaluates to the string you're after...

Anonymous
Not applicable
Author

It does not work... Like this the result is null...

I also tried

MaxYear= '[date.autoCalendar.Year]={' & $(=max(Year(date))) & '}

with the same result 😞

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

The variable created in the SET will be incorrect because the $() will get substituted during the script run. You should be able to see this problem by looking at the variable in the variable overview.  To avoid the $() substitution in the script you have to use one of these trick techniques.

LET MaxYear= '[date.autoCalendar.Year]={$' & '(=max(Year(date)))}';


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

kvanandan1
Creator
Creator

Hi,

Please use

Let Max_date_yr = max(Year(date));

Let MaxYear = '[date.autoCalendar.Year]={$(Max_date_yr)}' ;

and then

Count( {$<$(=MaxYear)>}distinct ID)

Anonymous
Not applicable
Author

Oh, thanks a lot. This works!

Thank you very much!

Petra

Anonymous
Not applicable
Author

This does not help. But thanks for your suggestions!

oknotsen
Master III
Master III

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post; if you can't see it, be sure to open the actual topic) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .

May you live in interesting times!