8 Replies Latest reply: May 3, 2012 7:35 AM by Dennis Hoogenboom RSS

    getting value wrong in straight and pivot table

    Sumit Thakur

      in expression i have used aggr function on country and region name, i get value 99.24 in straight table whereas 90.23 in pivot table.

        • getting value wrong in straight and pivot table
          swuehl

          It's really hard to say anything.

          Could you post a small sample app or at least your dimensions / expressions / chart settings?

          Also a screen shot might be helpful.

           

          One usual suspect is that in a straight chart, you can select a total mode like 'average', while in a pivot, the total mode is always expression total.

            • Re: getting value wrong in straight and pivot table
              Sumit Thakur

              expression used in bar chart as well as in line chart

               

              =avg(aggr(if(vMA_by=0,

              if(WRD_VAR=0,

              avg({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}Z5_MA_DOC),

              if(WRD_VAR=1,

              avg({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}Z5_MA_COD)

              )),

              if(vMA_by=1,

              if(WRD_VAR=0,

              avg({$<Gen_Yr_Mn=  {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}MAX_Z5_MA_DOC),

              if(WRD_VAR=1,

              sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Z_MAX_Z5<>0,if(Gen_DATE>=MA_COD,Z_Z4),0))/

              sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=CODand Z_MAX_Z5<>0,Z_MAX_Z5,Null()))*100)),

              if(vMA_by=2,

              if(WRD_VAR=0,

              sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=MA_DOC,Z_Z4))/

              sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=MA_DOC,if(Flag_Z5=0,Z_Z5,if(Flag_Z5=1,Z_MAX_Z5))))*100,

              if(WRD_VAR=1,

              sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=MA_COD,Z_Z4))/

              sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=COD,if(Flag_Z5=0,Z_Z5,if(Flag_Z5=1,Z_MAX_Z5))))*100

              ))))),Gen_Month_Name,COMP_CODE,COUNTRY_NAME,REGION_NAME_SHORT,PLANT_NAME,WTG_RATING,Model,CONTROLER_TYPE,BLADE_TYPE,CUST_NAME_DESC))

               

               

               

              dimension in Bar chart-Region_Name_short

               

              dimension in line chart- if(Gen_Yr_Mn >=$(vlast12MonthYr) and Gen_Yr_Mn <= $(vmaxMasterYrMn) and Region_Name_Short='GUJ', GenDate_Mn)

               

              where:-

               

              vMinMasterYrMn- =max(Master_Year)&04

              vMaxMasterYrMn- =date(max(Date),'YYYY')&date(max(Date),'MM')

              vLast12MonthYr - =date(addmonth(date#(max(cal_Yr_Mn,'YYYYMM'), -11, YYYYMM,)


                • getting value wrong in straight and pivot table
                  swuehl

                  Sorry, I am lost.

                   

                  Haven't you reported a problem with straight vs. pivot table chart in your original post?

                   

                  If you say: 'value 99.24', is this a total value or an expression value for a single line?

                    • getting value wrong in straight and pivot table
                      Sumit Thakur

                      expression used in linechart ,pivot table ,straight table

                       

                      =avg(aggr(if(vMA_by=0,

                      if(WRD_VAR=0,

                      avg({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}Z5_MA_DOC),

                      if(WRD_VAR=1,

                      avg({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}Z5_MA_COD)

                      )),

                      if(vMA_by=1,

                      if(WRD_VAR=0,

                      avg({$<Gen_Yr_Mn=  {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}MAX_Z5_MA_DOC),

                      if(WRD_VAR=1,

                      sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Z_MAX_Z5<>0,if(Gen_DATE>=MA_COD,Z_Z4),0))/

                      sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=CODand Z_MAX_Z5<>0,Z_MAX_Z5,Null()))*100)),

                      if(vMA_by=2,

                      if(WRD_VAR=0,

                      sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=MA_DOC,Z_Z4))/

                      sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=MA_DOC,if(Flag_Z5=0,Z_Z5,if(Flag_Z5=1,Z_MAX_Z5))))*100,

                      if(WRD_VAR=1,

                      sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=MA_COD,Z_Z4))/

                      sum({$<Gen_Yr_Mn= {">=$(vminMasterYrMn)<=$(vmaxMasterYrMn)"}>}if(Gen_DATE>=COD,if(Flag_Z5=0,Z_Z5,if(Flag_Z5=1,Z_MAX_Z5))))*100

                      ))))),Gen_Month_Name,COMP_CODE,COUNTRY_NAME,REGION_NAME_SHORT,PLANT_NAME,WTG_RATING,Model,CONTROLER_TYPE,BLADE_TYPE,CUST_NAME_DESC))

                       

                       

                      dimension in line chart,Pivot table,straight table- if(Gen_Yr_Mn >=$(vlast12MonthYr) and Gen_Yr_Mn <= $(vmaxMasterYrMn) and Region_Name_Short='GUJ', GenDate_Mn)

                       

                      where:-

                       

                      vMinMasterYrMn- =max(Master_Year)&04

                      vMaxMasterYrMn- =date(max(Date),'YYYY')&date(max(Date),'MM')

                      vLast12MonthYr - =date(addmonth(date#(max(cal_Yr_Mn,'YYYYMM'), -11, YYYYMM,)


                      SO WHEN I DO FAST CHANGES BETWEEN LINE CHART , PIVOT TABLE AND STRAIGHT TABLE THE VALUES DIFFER BETWEEN STRAIGHT TABLE AND PIVOT TABLE(LINE CHART)

                       

                       

                       

                      SO PLEASE HELP ME OUT