6 Replies Latest reply: Aug 9, 2011 9:06 AM by Paul Geudens RSS

    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 !