Calcul expression with a dimension Data Island

[Anonymous]

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

[Gysbert_Wassenaar]

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

[bbi_mba_76]

Hi,

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

Is not possible to link the tables ?

[Anonymous]

This works but it selects me that the period 0 

[Anonymous]

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

[#6]

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)) ?

Please check on attached?

Regard

[#7]

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.

[Anonymous]

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.

[#9 · Accepted solution]

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

Please find attached.

Kind Regards.

[Anonymous]

Yes! Thank you for your help.