Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
Hi,
Try this.
sum( {$<[DD1.IndexDate]=P(DateDim2.IndexDate)>} MeasureValue )
Regards,
Kaushik Solanki
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
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.
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 !!
Hi,
Try this.
sum( {$<[DD1.IndexDate]=P(DateDim2.IndexDate)>} MeasureValue )
Regards,
Kaushik Solanki
Thats it ! Thank you
Can you explain what it is ? Couldn't find it in reference after a quick look...
Thanks again !
Found it... Page 371 of manual reference...