Hello,

[#1]

Currently i am using the formula:

LET vNbInspectionECY2YearStowaways = 'sum({'&chr(36)&'<FACT_TYPE={' & chr(39) & 'INSPECTION' & chr(39) & '},DeficienceMainGroupCode={' & chr(39) & 'Stowaways' & chr(39) & '},Period_Path=,Period_Year = {">='&chr(36)&'(='&chr(36)&'(v_Last_Year))<='&chr(36)&'(=(max(Period_Year)))"} ,InspectionType={' & chr(39) & 'ECY' & chr(39) & '}>}NbInspection)';

giving me separte values for the previous year and the current year; but could you please help me use an " if  "condition in this formula and use" DeficienceMainGroupCode" for one year and "DeficienceGroupCode" for the other year?

[Peter_Cammaert]

Define two expressions instead of a single, one with set modifier Period_Year = {'$(=max(Period_Year)-1)'} and another one with the simple set modifier Period_Year = {'$(=max(Period_Year))'}.

In the first expression, use the corresponding aggregation field (you didn't specify which goes where) and again in the second expression.

Peter

[#3]

Hello Peter,

thanks for your response but could you plesae correct the below formula for me?
LET vNbInspectionECY2YearStowaways = 'sum({'&chr(36)&'<FACT_TYPE={' & chr(39) & 'INSPECTION' & chr(39) & '},DeficienceMainGroupCode={' & chr(39) & 'Stowaways' & chr(39) & '},Period_Path=,Period_Year = {'&chr(36)&'(=(max(Period_Year)))"} ,InspectionType={' & chr(39) & 'ECY' & chr(39) & '}>}NbInspection)';

[Peter_Cammaert]

I'm not going to, for a simple reason: the expression you posted has been awfully disfigured because your document uses variables in the expression field of charts. To create a new expression, you better start with testing the expression by entering it straight into the expression field of your chart. Afterwards you can still format the expression string so that it fits in a variable without undue $-substitution.

The two expressions can be:

=sum({$<FACT_TYPE={'INSPECTION'}, DeficienceMainGroupCode={'Stowaways'}, Period_Year = {'$(=max(Period_Year)-1)'}, InspectionType={'ECY'}>}NbInspection)

=sum({$<FACT_TYPE={'INSPECTION'}, DeficienceGroupCode={'Stowaways'}, Period_Year = {'$(=max(Period_Year))'}, InspectionType={'ECY'}>}NbInspection)

However, I'm almost certain that this will not produce the results you want. Not because the formulas are wrong (they're not) but because the logic is probably incorrect. You still didn't specify which field goes with what year. And do we select the same value in both DeficienceMazinGroupCode and DeficienceGroupCode to get a set in each case? Or do you want to create the two set selections and use them for both CY and LY, leading to four expression columns in all? In a pivot table?

Better post a screenshot of the chart you want to have.

[#5]

Hello Peter,

i am currently using a pivot table:

dimension: period_year(its NOT caluculated dimension)

expression:

(name)= storeways

formula used:   LET vNbInspectionECY2YearStowaways = 'sum({'&chr(36)&'<FACT_TYPE={' & chr(39) & 'INSPECTION' & chr(39) & '},DeficienceMainGroupCode={' & chr(39) & 'Stowaways' & chr(39) & '},Period_Path=,Period_Year = {">='&chr(36)&'(='&chr(36)&'(v_Last_Year))<='&chr(36)&'(=(max(Period_Year)))"} ,InspectionType={' & chr(39) & 'ECY' & chr(39) & '}>}NbInspection)';

so as result i am getting two coulmns for two years, with the corresponding values using the same expression;

but now i need to have the table in the same format except that previous year should use "DeficienceMainGroupCode" and the current year selcted in the application should use "DeficienceGroupCode".

could you plesae guide?

[Peter_Cammaert]

There are no other dimensions in this pivot table? So all you get is two cells with a value and two year headers?

[#7]

No, the only dimension is "period_year"..but  i have three columns:

one has the name of the event( ie: Stowaways etc), the next two has the corresponding values for event in the current year and the previous year.

[Peter_Cammaert]

Is the column with the Event Name then an expression as well? What does this expression look like?

[#9]

it uses the valriable: vNbInspectionECY2YearStowaways

where:

LET vNbInspectionECY2YearStowaways = 'sum({'&chr(36)&'<FACT_TYPE={' & chr(39) & 'INSPECTION' & chr(39) & '},DeficienceMainGroupCode={' & chr(39) & 'Stowaways' & chr(39) & '},Period_Path=,Period_Year = {">='&chr(36)&'(='&chr(36)&'(v_Last_Year))<='&chr(36)&'(=(max(Period_Year)))"} ,InspectionType={' & chr(39) & 'ECY' & chr(39) & '}>}NbInspection)';

i was wondering we if could modify the above like:

sum({$<FACT_TYPE={'INSPECTION'},Period_Path=,
Period_Year = {">=$(=$(v_Last_Year))<=$(=(max(Period_Year)))"} ,InspectionType={'ECY'},
DeficienceMainGroupCode={"if( Period_Year=$(=$(v_Last_Year)),
DeficienceMainGroupCode={'Stowaways'},DeficienceGroupCode={'Stowaways'})

>}NbInspection)

[Peter_Cammaert]

No.

1. Sets are assembled before the chart is recalculated. if (Period_Year... will return all years in Period_Year at the same time.
2. You cannot nest set analysis without E() or P().

That variable contains an expression to calculate sum(NbInspection). You said you have a column with the Event Name as well. Where does the Event Name come from in your pivot chart?