4

I have 4-5 tables of single and many rows per ID. I want to generate a summary table listing each ID along with various counts and max/mins, but I want to be able to filter on calculations. Example: "ID" is the identifier and there are two tables, TestA and TestB.

One desired selection criteria: Show only those IDs where at least one TestA score >5 and there is at least one TestB score.

In a straight table, this is simple to do with expressions, but the resulting table cannot be selected on the calculated true/false value.

I think I need to create a new table in the load script containing the ID, and then various conditions labeled as I wish. Then, these fields could be dimensions. This seems similar in concept to a master calendar. Am I on the right track?

If it helps to understand my example, this a medical application; the tables are lab results and other interventions that each require complex queries pulling data from various sources that are very "hard-coded" to produce a small data set from millions of rows of highly normalized source data. The desired dimensions would be combinations of the labs so as to allow identification of patients who meet certain criteria--then, once filtered, there would be many more graphs and charts to identify what tests and procedures were followed for that group of patients.

My current data model just loads many tables which then associate on ID. I had attempted to load all data into one big table using concatenates and calculations, but this did not seem to accomplish what I needed and was difficult to manage.

DaveA
  • 217
  • 3
  • 8

1 Answers1

1

IIUC, I think what you want to do can be accomplished with a combination of sliders/input boxes, variables and calculated dimensions in your table. The process is definitely burdensome, but it should allow you filter the way you want.

  1. Add a field to your table load statement in your script like rnum as RowNo().
  2. Create a variable for your filter(s). Ex. vFilterTestAScore.

  3. Add a slider or input box to your dashboard and point it to that variable.

    a. For slider, the option is in the General tab -> Data header -> select the Variable radio button.

    b. For input box, add the correct var from the list to the list of Displayed Variables.

  4. Set sliders/input boxes to the criteria you want: vFilterTestAScore = 5 and vFilterTestBScore = 1

  5. Create a straight table with ID as the dimension and expressions for TestAScore and TestBScore. The expression formulas would be sum(TestAScore) and sum(TestBScore) respectively (this won't make sense until the next step).

  6. Now add a calculated dimension to you table. The idea here is that rather than just having the ID dimension, you will create a calculated dimension that only displays the ID of the records that meet the criteria you select in the slider or enter in the input box. The formula should be something like: if(aggr(sum(TestA), rnum) >= vFilterTestAScore, ID, null()) or for multiple filters: if((aggr(sum(TestA), rnum) >= vFilterTestAScore) and (aggr(sum(TestB), rnum) >= vFilterTestBScore), ID, null()).

  7. On your new calculated dimension, check the 'Suppress When Value is Null' box so only results that meet your criteria are displayed in your table.

To summarize, you are using the variables to store your selection criteria which you are entering via input box or slider. Then you are conditionally displaying only the ID's in your table that match those criteria via a calculated dimension and 'Suppress When Null' option.

I can send you a .qvw if you aren't using the free personal edition and are able to open other qvw's.

bdiamante
  • 15,980
  • 6
  • 40
  • 46
  • Thanks, this is helpful...the basic concept of adding a calculated dimension to the table is a technique I have used which is effective for that table, but then I run into issues when I want to create other charts on the page. Example, the table lists patients who meet the criteria, then a chart might show, among those patients, what is the range of test scores. I think then, the solution is to put the same calculation into the expression. You do end up with a rather messy page when it would be so much conceptually easier to be able to select the calculated dimension. – DaveA Jul 10 '15 at 12:56
  • I see, the problem isn't getting this to work for one chart, but for multiple charts trying to operate under that same selection. What if you made list boxes with expressions as their fields instead of a dimension, and then put your 'selection logic' in that expression? The results of the list box would be ID's that you could then just manually select by click+dragging over all results. This way only the ID's matching the selection logic would be available in your State, so all other charts would update appropriately. – bdiamante Jul 10 '15 at 13:39
  • The listbox expression needs to be wrapped in an `aggr()`. So like: `=aggr(concat(distinct if((aggr(sum(TestA), rnum) >= vFilterTestAScore) and (aggr(sum(TestB), rnum) >= vFilterTestBScore), ID, null()), ','), ID)`. This takes the same `ID`'s returned, `concat` them and wrap them into a virtual table so the listbox can display the results. I tested this and for multiple charts and it should work. You'll just need to create a listbox for each scenario you want to run. – bdiamante Jul 10 '15 at 13:46