Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

lionelcanon
New Contributor II

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

1 Solution

Accepted Solutions
Not applicable

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


Please find attached.


Kind Regards.

9 Replies

Re: Calcul expression with a dimension Data Island

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


talk is cheap, supply exceeds demand
bbi_mba_76
Valued Contributor

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 ?


lionelcanon
New Contributor II

Re: Calcul expression with a dimension Data Island

This works but it selects me that the period 0

lionelcanon
New Contributor II

Re: Calcul expression with a dimension Data Island

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

Not applicable

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

Please check on attached?

Regard

Not applicable

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.

lionelcanon
New Contributor II

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.

Not applicable

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


Please find attached.


Kind Regards.

lionelcanon
New Contributor II

Re: Calcul expression with a dimension Data Island

Yes! Thank you for your help.

Community Browser