I think I found a similar question in the community but it was not answered due to lack of details (Filtering The percent of Total by Data Range). I want to calculate the percentage of total for a set of data which should be changing dynamically based on a date range.
So let’s say I have the following model table showing the row sales per day, department and country which looks like this and let’s name it: ‘raw_data’ table.model
I want to be able to calculate the percentage of each country per desk as per the below:
This is easy to do if I create a transform model based on the guidelines here: Calculate The Percent of Total in Holistics | Holistics Docs and create a transform model with the sum of sales grouped by desk. Then join the new transform model (let’s call it 'sales_by_desk) with the ‘raw_data’ model on Desk. This gives results regardless the date.
Now, if we want to add dates into the equation, here is where it becomes tricky. Following the use case 2 of the above guidelines link, we have two options:
Either join the two models based on the concatenation of Desk_Date
Create a new table for Dim_Dates and join the two tables based on the Dates 1 to many.
The problem is that this solution only works if we want to see the above percentages for each day separately. It won’t work if we add a filter to allow user to select custom ranges.
E.g. let’s say they want to select a range for July 2023 only. It should give something like this:
I haven’t been able to make it work as the above joins in this case give multiple combinations for dates and/or desks. Is there a solution/workaround to this problem?
If I understand your case correctly, you would like to achieve the following:
Create a pivot table that displays the percentage of each category in relation to the total value.
Enable end-users to dynamically select a date range to apply to the pivot table. The results in the pivot table will be adjusted accordingly based on the selected date range.
Currently, you have successfully addressed the first requirement by following the guide provided: Calculate The Percent of Total in Holistics. However, the second requirement remains unresolved.
Please let me know if I misunderstood anything.
If you are currently using Holistics 3.0 version and are interested in migrating to our 4.0 version, please feel free to contact our Support Team to request a version migration.
If you are currently using Holistics 3.0 and don’t want to migrate to our 4.0 version, I’m afraid that this issue is only solved when we support the Subgroup Calculation feature. This feature will enable you to calculate and display the percentage directly within the visualization, eliminating the need to create separate transform models as outlined in our guide.
However, at the moment, we have not had any plan to support it in the next 3-6 months because our main focus this year is to ship a complete as-code product.
Feel free to let us know if you need further support. We are happy to assist you
Many thanks for your response. You have understood the situation fully and correctly. Thanks for all the advice given.
We are currently under Holistics version 3.0. May I ask some information about migrating to version 4.0. I understand that this is a beta version based mostly on AQL queries. In case of migration, would the existing self service UI based reporting and transform models be preserved exactly as they are? Does 4.0 have the same features and functionality as 3.0 but with this optional additional features of 4.0?
Essentially, the 4.0 version can be seen as an evolution of the 3.0 version. However, due to significant architectural changes in the 4.0 version, not all features from 3.0 have been fully transitioned. For more details, you can check our Feature Gap Table here.
In case your team decides to move to our 4.0 version, please check our 4.0 Migration Doc to see the whole migration process.
Feel free to let me know if you need further assistance
