Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Set expression syntax question...

Hi

I have 1 table "TableX" which connects to 1 datedimension "DateDim1". I also have 1 date island "DateDim2" which is an exact copy of DateDim1 and not connected. The user selects a MonthYear in a box connected to DD2, it updates 2 variables on the fly, one with the current selection and 1 with (selection minus X months). Only 1 DD2.IndexDate can be selected at a time.


This setup I use to show an evolution of this month compared with some previous months. It works fine the way I've implemented it. But I want to make the set expression shorter and more logical...

Selected DateDim2.IndexDate   = Aug - 2011

$(varSelection)                         = 31/08/2011          [= Max(DateDim2.IndexDate)  ]

$(varMinXMonths)                     = 31/05/2011          [= AddMonths(Max(DateDim2.IndexDate),-3)  ]

So for all evolution charts I use the following set expression which works just fine:

    sum( {$<[DD1.IndexDate]={ ">=$(varMinXMonths) <$(=AddMonths(DateDim2.IndexDate,1))" }>} MeasureValue )

But in all non evolution charts I would like to show only the selected DateDim2.IndexDate. It works for me by doing:

    sum( {$<[DD1.IndexDate]={">$(=AddMonths(DateDim2.IndexDate,-1)) <$(=AddMonths(DateDim2.IndexDate,1))"}>} MeasureValue )

SO DD1.IndexDate needs to be equal to DD2.IndexDate for these charts. I tried the following, all without succes:

     sum( {$<[DD1.IndexDate]={DateDim2.IndexDate}>} MeasureValue )

     sum( {$<[DD1.IndexDate]={Only(DateDim2.IndexDate)}>} MeasureValue )

     sum( {$<[DD1.IndexDate]={"=Only(DateDim2.IndexDate)"}>} MeasureValue )

     sum( {$<[DD1.IndexDate]={AddMonths(DateDim2.IndexDate,0)}>} MeasureValue ) (with previous " " - variant)

     sum( {$<[DD1.IndexDate]={Max(DateDim2.IndexDate,0)}>} MeasureValue ) (with previous " " - variant)

     sum( {$<[DD1.IndexDate]={$(varSelection)}>} MeasureValue )

My gues is I make a small syntax mistake somewhere...

Any ideas?

Thanks in advance !

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Set expression syntax question...

Hi,

    Try this.

    sum( {$<[DD1.IndexDate]=P(DateDim2.IndexDate)>} MeasureValue )

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

6 Replies
Highlighted
Not applicable

Set expression syntax question...

Hi,

The correct syntax should be:

sum( {$<[DD1.IndexDate]=[DD2.IndexDate]>} MeasureValue )

or

sum( {$<[DD1.IndexDate]={$(=[DD2.IndexDate])}>} MeasureValue )

The problem with this is that it only works when only one value for DD2.IndexDate is selected. As soon as mutliple values are selected this does not work anymore.

As far as I know there is not really a solution for this.

Best regards,

Bert

Highlighted
Not applicable

Set expression syntax question...

Hi pgeudens,

I think i know the problem.

could you pls attach a sample or a subset of your application, that would help a lot.

Highlighted
Not applicable

Set expression syntax question...

Bert,

I tried both your solutions, unfortunately they didn't work... -> No data to display...

And as you said, there can only be 1 DD2.IndexDate selected at any time.

Abhinava Chandra, I'm working on an example... can still take a while...

Do you already have any suggestion that I can try out ?

Thanks both of you guys !!

Highlighted
MVP & Luminary
MVP & Luminary

Set expression syntax question...

Hi,

    Try this.

    sum( {$<[DD1.IndexDate]=P(DateDim2.IndexDate)>} MeasureValue )

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Highlighted
Not applicable

Set expression syntax question...

Thats it ! Thank you

Can you explain what it is ? Couldn't find it in reference after a quick look...

Thanks again !

Highlighted
Not applicable

Set expression syntax question...

Found it... Page 371 of manual reference...