Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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