How to ignore a dimension inside a pivot table

[LuisRivera]

Dear all,

I hope you can help me with a specific view for my enterprise.

In the following table I have the amount of orders I activated each month against the amount of cancelled orders.

Please note that we have 2 main dimensions used in the table: [Activation date] and [Cancel date]. I am also measuring the distinct count of [Order ID].

The main goal here is to measure the percentage of cancelled orders (based on the total activations in each month), just like in the example below (brutely made in Excel just to exemplify what I need to see):

For example, in the month of october, we cancelled 17% of the 48 orders activated in october/21 (remember the 8 we had in the previous table? 8 divided by 48 is 17%). In the following month, we cancelled 6% of those 48 orders (3/48=6%). 

I have tried several approaches but haven't managed to get there yet (while consulting QlikCommunity): the aggr() function, set analysis, total<dim>, and all<dim>.

Does anyone have a clue of how to get there in Qliksense/view?

Thanks in advance!

I'll post here a sample of the data used. Please let me know if there's any need of additional information.

Regards,

[LuisRivera · Accepted solution]

I managed to do it with 

Count([Order ID]) /
avg( aggr(Count(total <[Activation date]> [Order ID]),[Order ID],[Activation date]))

Thanks for whoever read and tried this out.