I have been trying to figure out how to calculate and group values of a dynamic dimension. Here is the story.
I created a dimension in Lookml for getting the release_number field from a table with setting the full_suggestion to YES. therefore, this dimension is going to return me a list of release numbers and also give a list of suggestions in the filter for users. Because its values can be updated once a while in the applications, so I call it a dynamic dimension(...not sure if there is a standard term for it...).
Here is the goal. I need to use this dimension to do some simple calculations/measures based on a user’s input; if a user selects a 2021.21 release number, as an example, then I next will need to get a total count of the messages that are from this particular release number. Not only I need to get the total count of this release number but also display the total count of messages that contain the rest of the release numbers, basically, group the rest of the release numbers in a single column.
At this point, there should be 3 columns in a table - messages, the total count of 2021.21(selected by the user), & the total count of the rest of the release numbers.
once I have the counts all set, a simple calculation is the next. I need to calculate the percentage of this particular release number compare to the overall. For example, if the total count of 2021.21 release number is 10, and the rest of the release numbers has a total count of 20, then the percentage of this particular release number is 10/(10+20), which is about 33.333%.
As a result, I should have a table that contains 4 columns: messages, the total count of user-selected release number, user-selected release number percentage, and a total count of the rest of the release numbers.
Currently, I am at where I have pivoted the release_number dimension and displaying all release numbers with the count of each release number and their percentage using the Totals and Row Totals features. but I am adding too many columns since there are more than 2 release numbers. Thus, I am stuck at where how to group the rest of the release numbers as a single column besides the one user selected.
I know that creating another view and join them together should do the work, but that approach right now is not supported due to the query behind the scene is exceeding resources (for getting the correct message have taken a lot of resources and it has to be done that way, at least by far).
If I just select the particular release number in the filters section, then the results section only contains this particular release number count and also results in a 100% percent calculation since there is no other data to compare with.
Looking for some help...anybody has good ideas to solve this issue? such as how to create a dimension that users can interact with? and then use the selected release number in a case when dimension to call a measure (count) for that particular release number, and then also create another measure for the rest of the release number?