How to ignore a dimension inside a pivot table

LuisRivera — Thu, 03 Mar 2022 19:59:14 GMT

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,

Re: How to ignore a dimension inside a pivot table

LuisRivera — Thu, 10 Mar 2022 19:01:43 GMT

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.

topic Re: How to ignore a dimension inside a pivot table in App Development

How to ignore a dimension inside a pivot table

Re: How to ignore a dimension inside a pivot table