topic Set expression syntax question... in QlikView

Set expression syntax question...

Tue, 09 Aug 2011 08:36:30 GMT

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 !

Set expression syntax question...

Tue, 09 Aug 2011 09:44:20 GMT

Hi,

The correct syntax should be:

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

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

Set expression syntax question...

Tue, 09 Aug 2011 09:46:12 GMT

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.

Set expression syntax question...

Tue, 09 Aug 2011 10:14:01 GMT

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 !!

Set expression syntax question...

kaushiknsolanki — Tue, 09 Aug 2011 12:39:03 GMT

Hi,

Try this.

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

Regards,

Kaushik Solanki

Set expression syntax question...

Tue, 09 Aug 2011 12:56:49 GMT

Thats it ! Thank you

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

Thanks again !

Set expression syntax question...

Tue, 09 Aug 2011 13:06:20 GMT

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