13 Replies Latest reply: Aug 10, 2016 7:23 AM by Amit Saini RSS

    Pivot table Layout help???

    Amit Saini

      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

        • Re: Pivot table Layout help???
          Rudolf Linder

          use set Analysis something like

          sum({<Plant_Short_...=>}yourvalue)

           

          you need to insert your fullname of the Dimension "Plant_Short_)

          means, Show value regardless of selection of Plant_short_..

          • Re: Pivot table Layout help???
            Sunny Talwar

            The other plants are still available they just move to the back of selected plant? Are you using a sorting expression? May be you have to ignore selection in your sorting expression using {1}

              • Re: Pivot table Layout help???
                Amit Saini

                Hi Sunny,

                 

                Only selected Plant is available on selection , can't see other Plants.

                 

                Yes I'm doing below sorting on Plant orders based on Customer requirement:

                 

                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

                  • Re: Pivot table Layout help???
                    Sunny Talwar

                    Can you try this:

                     

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

                      • Re: Pivot table Layout help???
                        Amit Saini

                        Sorry not working

                         

                        Thanks,

                        AS

                          • Re: Pivot table Layout help???
                            Sunny Talwar

                            Do you mind putting this things here:

                             

                            1) Dimension (list all dimensions whether field or calculated dimensions)

                            2) Expression

                            3) Sorting Expression

                              • Re: Pivot table Layout help???
                                Amit Saini

                                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

                                  • Re: Pivot table Layout help???
                                    Sunny Talwar

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

                            • Re: Pivot table Layout help???
                              Sunny Talwar

                              And may be with this expression (just for testing)

                              Sum({1}Value)

                          • Re: Pivot table Layout help???
                            Alluraiah Allu

                            Hi

                             

                            If you want your PVIOT TABLE not changed irrespective any selections .

                             

                            Please try this in the  Properties ...>General...>(select)Detached

                            • Re: Pivot table Layout help???
                              Amit Saini

                              Thank u Sir