Hi all, I donโt know how to concisely put my query here, so here goes (Sorry for the lengthy description)!
Thereโs a table called โSurgery Logbookโ in my app. In it, there is a set of columns called [Surgery Title], [Specify - 1], [Specify - 2], [Specify - 3], which fetch data from a table called โSurgery Listโ. So basically they work as dependent dropdowns. When the user picks a surgery title from [Surgery Title] dropdown, then either [Specify - 1], [Specify - 2] or [Specify - 3] will appear for further specification of that surgery depending on the dropdown pattern created in the โSurgery Listโ. So this โSurgery Logbookโ table has a lot of data pertaining to each surgery user inputs.
Now what I want is to create a few charts using the data in the โSurgery Logbookโ. I created a separate table called โAnalyserโ for this purpose where the user gets to select which kind of chart/comparison of data he/she wants (I have created an Enum column with different choices ranging from Chart 1 to Chart 7, each chart comparing a certain variable taken from the โSurgery Logbookโ tableโs data, i.e: Surgery vs Complication Rate, Surgery vs Age Distribution etc., and when the user selects one of the Chart options, a specific chart gets created, and the user gets to decide which surgery titles that chart will contain).
So one of the charts I wanted to create was to compare surgeries and their complication rates. But I wanted the user to select which surgeries are to be compared. So I created a form for that inside the โAnalyserโ where the user gets to input a list of surgeries. Since the best way to create a list of surgeries is by creating a โreferenceโ table, I created a table called โMultiple Surgeriesโ where I again created columns called [Surgery Title], [Specify - 1], [Specify - 2], [Specify - 3] fetching the same dropdown data from the โSurgery Listโ table as I did for the โSurgery Logbookโ, so this way, the user can really specify the surgeries he/she wants to compare. (eg: If surgery title is โAnterior Resectionโ, the [Specify - 1] column allows to further specify it as โLaparoscopically doneโ or โdone as open surgeryโ). Then I created a slice for the โSurgery Logbookโ, with a Row Filter Expression to filter and show only the data pertaining to the Surgery Titles and specifications user selects via that โMultiple Surgeriesโ reference table using a LOOKUP expression. This works perfectly for me!
Only issue is, if I want to compare the same surgeryโs 2 different arms (for example, Anterior resection surgeryโs laparoscopy arm vs open arm), although user can create 2 rows in the โMultiple Surgeriesโ table, 1st row with [Surgery Title]=Anterior Resection and [Specify - 1]=โLaparoscopicโ, and 2nd row with [Surgery Title]=Anterior Resection and [Specify - 1]=โOpenโ, and even if the LOOKUP expressions have been written to fetch Row 1 and Row 2 data separately in to the Slice for the Row Filter Expression, it still combines both rows together and show as a single entity - Anterior Resection in the chart, and not as 2 different Anterior Resections. Is there a way to mitigate this issue? Everything works perfectly as long as [Surgery Title] brings up different surgery titles, the problem comes only when the same surgery titleโs further specifications are different.
LinkedIn
Twitter
