Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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