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?

[#2]

Hello,

could you plesae help me fix the formula:

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

[#3]

the types of events are calculated from expressions(example: the formula provided for Storeways, which is a kind of event.)

[Peter_Cammaert]

This is a bit confusing. First there were Event Names present in a separate column in your chart, now you are talking about Event Types that are calculated by the formula... Do you mean to say that you have a separate Pivot table for Stowaways (or is it Storeways?) and other Pivot tables for the other Event types?

[#5]

currentlyi have a pivot table with:

dimension: period_year

expressions: (type of events)-> (ex: storeways, navigation etc) and one such formula used for this currently is:

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 this will result in a pivot table with one column having the "type of events", the next two will be v_Last_Year and Period_Year respctively and these two columns will have the sum value for the corresponding  "type of events".

ie. the same formula used to calculate the value for both the years, cause we are taking into consideration

:Period_Year = {">='&chr(36)&'(='&chr(36)&'(v_Last_Year))<='&chr(36)&'(=(max(Period_Year)))"},

but current requiremnt is for the "v_Last_Year", we need to use the filed: "DeficienceMainGroupCode"; but for "max(Period_Year), we need to use the filed: "DeficienceGroupCode";

so could you please suggest?

[Peter_Cammaert]

Can you post a screenshot of this particular pivot table in this discussion?

[#7]

[Peter_Cammaert]

Ah, that makes a few things a lot clearer. AFAIK you do not need a pivot table since you can rotate a straight table 90 degrees and get the same effect.

Try with this formula. Put it straight in the expression field for 'Navigation'. You can keep the current content by commenting it out (put a double forward slash in front)

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

which will compose a set with records that either belong to LastYear and DeficienceMainGroupCode = 'Stowaway', OR belong to the CurrentYear and DeficienceGroupCode='Stowaway'. The LastYear selector doesn't use you v_Last_Year variable yet, but that can be changed later on.

[#9]

Hello Peter,

that does make it simpler, thankyou; but we dont seem to be getting data for the previous year; there is a record for the current year only.