This is an interesting challenge for v9.
You haven`t specify what type of visual you were looking for, so here is what I came with.
I am sure others would have different solutions, I suggest that you post your question on the beta9 forum, you have better chances to reach v9 beta users.
Have a look and let me know if you have questions.
Parameters let you choose 2 Sub_Categories, the first column shows the number of customers who bought the first category, the second column shows the number of customers who bought both, and the third column shows the ratio.
v9 doesn't add much here. The way I'd do this is with 2 parameters for choosing the category & state, then a conditional Set on Customer that is something like SUM(IF [Category] == [categoryParam] AND [State] == [stateParam] THEN 1 ELSE 0 END ) > 0.
Then you have a set of customers who bought the product that is computed as a query outside the regular query and is available as a regular dimension in the view for comparison to everyone else:
While this may have some performance limitations in very large data sets on DBs that don't do subqueries well, an advantage over the table calculation solution is that further calculations are quite easy because the results of the cohort calc are available as a dimension. For example, a % diff can be done in 2 clicks with the Set based solution, it would require a custom calculation and solid knowledge of table calcs to do it in the table calc solution.
If you want to separate the State from Category selections, then you can use additional Sets. In v9 you can swap out the Sets for the new LOD calculations, they do something very similar to conditional & top Sets.
8.3 workbook is attached.
cohort from set.twbx 993.6 KB
I did some modifications from my previous attachment to get rid of the Customer ID and Sub-Category dimension in the view , and take advantage of the LOD functions to answer the questions;
For two given States, what is the percentage of customers who bought from one Sub-Category, who did also by from the second Sub-Category ?
So even if granularity is needed at the customer ID for the computation, the results are displayed only at the State level, and the Customer ID and Sub-Category dimensions don`t need to be added in the detail of the view.
Hi Jonathan! Reaching out to you again on this old text table question -- your solution is an excellent one! I am still looking for a method that would allow multiple criteria to be selected, however. It looks like parameters only allow one value to be selected at a time? So there's no way to specify, say "people in California who bought appliances AND bookcases."
Do you know if there's any way to do this? I'm attaching a tweaked version of the workbook you originally uploaded (my ideal goal would be to have the rows/columns remain static, while the numbers change depending on the values/parameters selected). I'm really struggling with figuring out the best way to drill down into specific populations using multiple criteria. Maybe I'm thinking about this the wrong way?
cohort from set - DW.twbx 1,009.8 KB