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