9 Replies Latest reply: Oct 30, 2013 4:28 AM by lionel canon

# Calcul expression with a dimension Data Island

Hello,

I created a data island that I use as dimension.

I would like to relate the value of the dimension in the calculation of my expression.

dimension:

DateAbo

PeriodeFacturation

Expression:

sum ({<DateFact={"\$(=date(max(DateAbo)+7*PeriodeFacturation))"}>} Nb)

This expression works if I select a PeriodFacturation. How can it work without selection?

thank you

• ###### Re: Calcul expression with a dimension Data Island

Maybe like this: sum({<DateFact={"\$(=date(max(DateAbo)+7*min(PeriodeFacturation)))"} >} Nb)

• ###### Re: Calcul expression with a dimension Data Island

This works but it selects me that the period 0

• ###### Re: Calcul expression with a dimension Data Island

Hi,

working with data island, which value of PeriodeFacturation should be taken to calculate the expression?

Is not possible to link the tables ?

• ###### Re: Calcul expression with a dimension Data Island

It is possible but the calculation takes several hours ...

• ###### Re: Calcul expression with a dimension Data Island

Hi Canon,

I would think this only work if you have a variable that would return a default DateFact? Say you have variables

vMaxPerFact = 7* max(PeriodeFacturation) ; and

vMaxDateAbo  =max(DateAbo); you will then have a variable for your default DateFact:

vDefaultDateFact =mid(date(vMaxDateAbo+vMaxPerFac),1,10); you will notice that your default DateFact (without any Selection made) is ' 05/03/2013 ' which would not return any Nb value because it does not exist in the FACT table. And so you can have an expression such as: sum(if(DateFact=vDefaultDateFact,Nb)) ?

Regard

• ###### Re: Calcul expression with a dimension Data Island

Furthermore, the expression sum ({<DateFact={"\$(=date(max(DateAbo)+7*PeriodeFacturation))"}>} Nb) can not return any value withouth a selection on PeriodeFacturation because the field PeriodeFacturation returns multiple values.

• ###### Re: Re: Calcul expression with a dimension Data Island

Thanks Quentin for your intervention but it doesn't work.

Yes, in my expression, PeriodeFacturation return multiple values so I'm looking how to retrieve the value that there in my dimension.

• ###### Re: Calcul expression with a dimension Data Island

Then I think you should use a sum expression with a if statement instead of the set analysis:

Fist of all create the variable vMaxDateAbo = date(max(DateAbo)) .

Then use the following expression in your straight table:

sum(if (DateFact = (vMaxDateAbo + 7*PeriodeFacturation) , Nb))