topic Calcul expression with a dimension Data Island in QlikView

Calcul expression with a dimension Data Island

Anonymous — Mon, 28 Oct 2013 14:35:05 GMT

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

Gysbert_Wassenaar — Mon, 28 Oct 2013 15:43:45 GMT

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

Re: Calcul expression with a dimension Data Island

bbi_mba_76 — Mon, 28 Oct 2013 15:44:05 GMT

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

Anonymous — Tue, 29 Oct 2013 08:40:34 GMT

This works but it selects me that the period 0 

Re: Calcul expression with a dimension Data Island

Anonymous — Tue, 29 Oct 2013 08:41:19 GMT

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

Re: Calcul expression with a dimension Data Island

Tue, 29 Oct 2013 10:14:31 GMT

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

Re: Calcul expression with a dimension Data Island

Tue, 29 Oct 2013 10:30:54 GMT

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

Anonymous — Tue, 29 Oct 2013 15:32:16 GMT

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

Tue, 29 Oct 2013 17:01:00 GMT

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.

Re: Calcul expression with a dimension Data Island

Anonymous — Wed, 30 Oct 2013 08:28:08 GMT

Yes! Thank you for your help.