8 Replies Latest reply: Jun 20, 2017 4:57 AM by apoorva srivastava RSS

    Show Only rows greater than zero in pivot table

    apoorva srivastava
      DealerNameRegionStateCityChannelCompetitorVolValSEC Count
      ANDD000084
      SANGEETHA MOBILES PVT LTD
      SOUTH 2
      ANDHRA PRADESH
      KURNOOL
      MT
      OPPO
      2
      20400
      1
      ANDD000084
      SANGEETHA MOBILES PVT LTD
      SOUTH 2ANDHRA PRADESHKURNOOLMT
      OTHERS
      162000

       

       

       

      Hi all ,

      I need to show record of a dealer only where SEC Count expression is greater than 0 .  No record should be shown for 0 sec count .

      In Sec Count i m doing 2 things:

      1)Sum({<CountershareType={'SecCountCompetitors'}>}UserResponse)

      2)Count(DISTINCT SECCode)

       

      Overall expression:

       

      =if(Flag='Samsung',Count(DISTINCT SECCode), if(Flag='Competitors',Sum({<CountershareType={'SecCountCompetitors'}>}UserResponse), (sum({<CountershareType={'SecCountCompetitors'}>}UserResponse)+Count(DISTINCT SECCode))))