Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
Below is my Pivot table :
Result when no specific "Plant" is selected from list
Now if I select any Plant from list say "KBC" , I'm having below results:
Here above "KBC" shifted right after Dimension after selection from Plant list
Desired Layout should be like below : The position of columns should not change based on any selection
Please help hoe this can be done!
Thanks,
AS
Do you mind putting this things here:
1) Dimension (list all dimensions whether field or calculated dimensions)
2) Expression
3) Sorting Expression
Hi,
1) Dimension (list all dimensions whether field or calculated dimensions):
Plant_Short_Name
2) Expression
Expression1:
=Sum({$ <
Period_Type={'R12'}
,Year_EHS=, Quarter=,Month_EHS=, Year_Qtr=, Year_EHS=, Date_EHS=
,AsOf_Year_Month={"$(=Date(Max({$} AsOf_Year_Month),'YYYY-MM'))"}
// ,AsOf_DateValue={">=$(=MonthStart(AddMonths(Max({$} AsOf_DateValue),-11)))<=$(=Max({$} AsOf_DateValue))"}
// ,active_site={1,2}
,%row_type_code={4}
,$(vDailyLogDims)
,$(vIssueTrackingDimensions)
>}
TRI * $(vInjRateFactor))
/
Sum({$ <
Period_Type={'R12'}
,Year_EHS=, Quarter=,Month_EHS=, Year_Qtr=, Year_EHS=, Date_EHS=
,AsOf_Year_Month={"$(=Date(Max({$} AsOf_Year_Month),'YYYY-MM'))"}
// ,AsOf_DateValue={">=$(=MonthStart(AddMonths(Max({$} AsOf_DateValue),-11)))<=$(=Max({$} AsOf_DateValue))"}
// ,active_site={1,2}
,%row_type_code={4}
,$(vDailyLogDims)
,$(vIssueTrackingDimensions)
>}
Manhours)
Expression2:
if(sum([Act Planned Production Hours]) = 0, avg([Act OEE]),
sum([Act OEE]*[Act Planned Production Hours])/sum([Act Planned Production Hours]))
Expression 3:
=If( if(Year=2013,avg([Act. Tanks Scrapped as % of Tanks Produced]),(Sum([Act. Tanks Scrapped (Total Line)])/sum([Act BMM Shells Produced])))<>0,
if(Year=2013,avg([Act. Tanks Scrapped as % of Tanks Produced]),(Sum([Act. Tanks Scrapped (Total Line)])/sum([Act BMM Shells Produced]))),
null()
)
3) Sorting Expression
Dual(Plant_Short_Name,Match(Plant_Short_Name,'KB2','KBC','KBK','KBL','KIS','KBO','KBR','KDU','KHE','KIS','KKS','KOF','KRO','KUK','KUL','KWK','KDT','CWC','KLA','KMX','KOC',
'KSI','KTX','KUS','KCC','KCQ','KSH','KGZ','KJP','KZC'))
Thanks,
AS
Try out these changes:
Expression1:
=Sum({$ <
Period_Type={'R12'}
,Year_EHS=, Quarter=,Month_EHS=, Year_Qtr=, Year_EHS=, Date_EHS=, Plant_Short_Name=
,AsOf_Year_Month={"$(=Date(Max({$<Plant_Short_Name=>} AsOf_Year_Month),'YYYY-MM'))"}
// ,AsOf_DateValue={">=$(=MonthStart(AddMonths(Max({$<Plant_Short_Name=>} AsOf_DateValue),-11)))<=$(=Max({$<Plant_Short_Name=>} AsOf_DateValue))"}
// ,active_site={1,2}
,%row_type_code={4}
,$(vDailyLogDims) -> Not sure what this variable contains
,$(vIssueTrackingDimensions) -> Not sure what this variable contains
>}
TRI * $(vInjRateFactor)) -> Not sure what this variable contains
/
Sum({$ <
Period_Type={'R12'}
,Year_EHS=, Quarter=,Month_EHS=, Year_Qtr=, Year_EHS=, Date_EHS=, Plant_Short_Name=
,AsOf_Year_Month={"$(=Date(Max({$<Plant_Short_Name=>} AsOf_Year_Month),'YYYY-MM'))"}
// ,AsOf_DateValue={">=$(=MonthStart(AddMonths(Max({$<Plant_Short_Name=>} AsOf_DateValue),-11)))<=$(=Max({$<Plant_Short_Name=>} AsOf_DateValue))"}
// ,active_site={1,2}
,%row_type_code={4}
,$(vDailyLogDims)-> Not sure what this variable contains
,$(vIssueTrackingDimensions)-> Not sure what this variable contains
>}
Manhours)
Expression2:
if(sum({$<Plant_Short_Name=>}[Act Planned Production Hours]) = 0, avg({$<Plant_Short_Name=>}[Act OEE]),
sum({$<Plant_Short_Name=>}[Act OEE]*[Act Planned Production Hours])/sum([Act Planned Production Hours]))
Expression 3:
=If( if(Only({$<Plant_Short_Name=>}Year)=2013,avg({$<Plant_Short_Name=>}[Act. Tanks Scrapped as % of Tanks Produced]),(Sum({$<Plant_Short_Name=>}[Act. Tanks Scrapped (Total Line)])/sum({$<Plant_Short_Name=>}[Act BMM Shells Produced])))<>0,
if(Only({$<Plant_Short_Name=>}Year)=2013,avg([Act. Tanks Scrapped as % of Tanks Produced]),(Sum({$<Plant_Short_Name=>}[Act. Tanks Scrapped (Total Line)])/sum({$<Plant_Short_Name=>}[Act BMM Shells Produced]))),
null()
)
3) Sorting Expression
Dual(Plant_Short_Name,Match(Only({1}Plant_Short_Name),'KB2','KBC','KBK','KBL','KIS','KBO','KBR','KDU','KHE','KIS','KKS','KOF','KRO','KUK','KUL','KWK','KDT','CWC','KLA','KMX','KOC','KSI','KTX','KUS','KCC','KCQ','KSH','KGZ','KJP','KZC'))
Thank u Sir