Hi all,
Please advise as I've been struggling with this task.
I need to create a pivot table demonstrating the relative percentage of each movement to the total movement per stage sensitive to some set analysis and selections by the user.
I've added an excel example with dummy data to demonstrate what I need.
The pivot table demonstrates a movement month-on-month over stages. Thus, the rows are the stage group the product was categorised as the previous month while the columns are the stage groups the products are now categorised as.
The resulting Qikview pivot table needs to indicate for each stage(rows) movement what the percentage move to the different stages in the next month (column headings) is. Thus, I need Qlikview to give me a table with the percentage of rows summing to 100% for each row (see table 2 in the excel example attached).
I currently can only get the Qlikview pivot table to correctly identify each row, but it fails to recognise the set analysis of only displaying the sum of the "WTD" scenario and thus gives me the total across ALL scenarios.
My current expression to achieve this is:
=(sum({<Scenario = {'WTD'}>}TransitionMatrix.sum_exposure_From))/(SUM(Aggr(SUM({$<[scenario] ={'WTD'}>} [TransitionMatrix.stage_from]),TransitionMatrix.Stage_From)))
Also note the table should be dynamic for the date, product and segment selected by the user and ONLY display the sum for the "WTD" scenario.
Thank you!
