I want to be able to filter a table based on the states assigned to the average score in the last column:
I’ve tried using the criteria that define the states and assigning a number (3, 2, 1, 0) to the result, then filtering using a “number range” filter. It’s not filtering as I’d expected, and I want the user to be able to filter based on the text names of the states anyway.
Is there an easy way to do this? Thanks!
Hi Thomas,
I’m curious as to what the solution to this would be myself (I felt like a click interaction on the legend would’ve somehow gotten the results) but I tried testing and couldn’t get it to work.
One thing you could do is setup a calculated element that defines the parameters of your different state groups (exceptional, passing, not meeting standard, no go).
For example if if ($average score$ >= 99) {return “Exceptional”;}
if ($average score$ < 99) {return “Not Meeting Standard”;}
You could then put that state status in your table, hide it and setup a filter for the status status.
It doesn’t quite get what you wanted but hope this helps some other time.
Thanks,
Derek
Derek,
Thanks for the idea, I think it would fit the bill if I could get it to work.
How do you return text using a formula? Dundas is accepting my formula (below), but I can’t get it to produce any output:
if (Math.Round(AVG($Average Score$,$Assoc + Username$,$audittype$),3) >= .98)
{return “Exceptional”;}
else if (Math.Round(AVG($Average Score$,$Assoc + Username$,$audittype$),3) >= .965)
{return “Meeting Standard”;}
else if (Math.Round(AVG($Average Score$,$Assoc + Username$,$audittype$),3) > 0)
{return “Not Meeting Standard”;}
else {return “No Go”;}
Are you doing it in the cube, maybe? That’d be difficult for us, as the score calculation is involved and would be messy to do in the cube.
If I understand correctly you won’t have to change the original cube, just add a calculated element to your cube design.
From here you are able to select a string as the data type:
Thanks, that’s what I suspected.
That won’t work for our purpose because the “average score” can be calculated several different ways…different time periods, different groupings of work type. We won’t be able to do that in the cube as a single calculation, so we can’t assign a string status against it.
I was able to assign number against the average score and then tell Dundas to display text instead of each number, so that “3” became “Exceptional”. But I still wasn’t able to filter against it, the filter had to be a number range filter.
So I’m still looking for a solution. I came from the Tableau world, where filtering by “state” is an automatic feature and dashboard designers could create their own calculated fields that returned either text or numbers.
Ahh I understand your frustration.
I haven’t messed around with this much but my final suggestion would be to take a look at this and see if you can get it working for your use case:
https://www.dundas.com/support/developer/script-library/controls/legend/legend-hit-test
Thanks,
Derek
Hi @thomas.1
I would probably define these states as actual dimensions through a Calculated Element in a DataCube. This will give you values to filter on the dashboard. The problem with the DundasScript via formulas (that you’ve no doubt discovered) is that it will only give you numbers. If you do this, then you need to manually show/hide data in your visualizations and it will cause additional work.
Definitely agree with @derek.sell here.
Jeff,
Thanks. Unfortunately the states are dependent on the time period selected, so we can’t calculate them in the cube.
I suppose the users could download the data and do the filtering in Excel? Based on the states…I’m assuming the state information is provided in the Excel download.
I’m not understanding why Dundas doesn’t allow formulas to return text values. I’m coming from the Tableau world, where that was common practice.
makes sense.
You can certainly use numbers and filter on them once they come from a formula. What if you returned something simple and filtered on those numbers? In this example, I just wanted an integer to be returned so I am returning the first number from my measure. You can see at the bottom that I am then filtering on the number that was returned.
I get that you wouldn’t want this experience for your user but you could also mask it and provide the user with a nice drop-down list that silently does the filtering on the number being returned via a script.
Example:
The user chooses “Option A” from the drop-down. The script executes and filters for ‘1.’
Another solution - could be to create a drop down with 5 elements:
And then have a filter interaction on the click event of the drop down - filtering the average score on the same values you use when setting up the states.
So if this is how you define your states:
if (Math.Round(AVG($Average Score$,$Assoc + Username$,$audittype$),3) >= .98)
{return “Exceptional”;}
else if (Math.Round(AVG($Average Score$,$Assoc + Username$,$audittype$),3) >= .965)
{return “Meeting Standard”;}
else if (Math.Round(AVG($Average Score$,$Assoc + Username$,$audittype$),3) > 0)
{return “Not Meeting Standard”;}
else {return “No Go”;}
You would instead create a formula in the metric set like this:
if (Math.Round(AVG($Average Score$,$Assoc + Username$,$audittype$),3) >= .98)
{return 3;}
else if (Math.Round(AVG($Average Score$,$Assoc + Username$,$audittype$),3) >= .965)
{return 2;}
else if (Math.Round(AVG($Average Score$,$Assoc + Username$,$audittype$),3) > 0)
{return 1:}
else {return 0;}
And then hide this formula expression column in the table
Then create a single number filter (so you get a viewparameter on it) and drag it off canvas (or just hide the control).
So here’s my example (just created a data set real quick for illustration purposes so my states are not exactly like yours)
Hope it might be useful for you 
Ole,
Thanks, that worked perfectly!
I had another stumble with one of my formulas. We discovered that this didn’t work reliably:
Math.Round($score$/$possible,3)
but that this did:
Decimal avgscr = $score$/$possible$;
return Math.Round(avgscr,3)
Thanks again for your help!
Tom
