0 Replies Latest reply: Jun 29, 2011 1:29 PM by Bikash Debnath RSS

    Expression change with Column change in Pivot Table

      Hi All,

      Good Morning;

      I have a new question to ask. Recently I was working with a pivot table. I have attached the picture of the pivot table how it looks.

      Now to calculate the % of Opportunity(last column) I have used the following expression. And its giving me the correct result. What it does is

      it calculates the percentage of value of individual row in that bucket for "Num of Opportunity" column. For example in 4th row of the table the

      value in Num of Opportunity is= 15, hence the % of Opportunity is => 15/139= 10.8% . Similarly for the next row its =>13/139= 9.4% and so on.

       

       

      Count(distinct {1<FACT_TYPE={'Current'},FLAG_HEALTH_REPORT={'1'}, REVENUE_RECOGNITION_FLAG ={'Yes'}, IS_PART_ACTIVE={'1'}, MODIFIED_DATE={"<=$(vMaxDate)"}>} OPP_ITEM)

      /

      Count(distinct {1<FACT_TYPE={'Current'},FLAG_HEALTH_REPORT={'1'}, REVENUE_RECOGNITION_FLAG ={'Yes'}, IS_PART_ACTIVE={'1'}, MODIFIED_DATE={"<=$(vMaxDate)"}>}total <STAGE,Bucket_Test> OPP_ITEM)

       

      Now here is the problem statement.

      When the user interchangeably moves the Design Sales Area column to Stage column position, the formula should actually change to

       

      Count(distinct {1<FACT_TYPE={'Current'},FLAG_HEALTH_REPORT={'1'}, REVENUE_RECOGNITION_FLAG ={'Yes'}, IS_PART_ACTIVE={'1'}, MODIFIED_DATE={"<=$(vMaxDate)"}>} OPP_ITEM)

      /

      Count(distinct {1<FACT_TYPE={'Current'},FLAG_HEALTH_REPORT={'1'}, REVENUE_RECOGNITION_FLAG ={'Yes'}, IS_PART_ACTIVE={'1'}, MODIFIED_DATE={"<=$(vMaxDate)"}>}total <Bucket_Test,STAGE> OPP_ITEM)

      in order to give the correct result.

       

      So how can I make the expression dynamic with the position of the column?

       

      Thanks & Regards,

      Bikash