-
1. Re: Add a formula filter to dashboard action filter?
Simon RuncAug 31, 2016 9:16 AM (in response to Adrian Lee)
hi Adrian,
So one way of doing this is to use a FIXED LoD. FIXED LoDs are calculated before a (regular dimension) filter is applied...
In the attached I created
[Claimant Rate Average]
{FIXED [Date]: AVG([JSA Claimant Rate])}
so this returns to every row the Average for each Month...which means when we filter on a Ward, it still has access to (and displays) the overall average for each month.
So the issue is, that your Average (in the E10000027 ward) isn't the same as the average as calculated from all the wards averages. I assume this is either to do with missing data, or that the data is weighted by population. If you still have (and can add to your data) the populations for each ward, we can amend this calculation (I think!...famous last words!) so take account of the weighting...but instead of that, we can do something a little more creative!
[Claimant Rate from E10000027]
{FIXED [Date]: AVG(IIF([Ward Code] = "E10000027", [SomersetRate],NULL))}
So this takes the Claimant Rate from E10000027, and applies it to all rows (NULLS get ignored in AVG...well all calculations!)...so again when the filtering is applied it still shows. I've put both versions on your chart (and sheet 3 shows you what's going on).
Hope this helps, and makes sense...please post back if not.
-
2. Re: Add a formula filter to dashboard action filter?
Adrian Lee Aug 31, 2016 9:42 AM (in response to Simon Runc)The data used is downloaded directly from NOMIS so I don't have access to the raw data, hence the Somerset figure being an additional row rather than an aggregation.
Some data is suppressed due to low numbers and they do rounding of the figures before calculating the rate, so later aggregation of the data isn't going to work.
This is becoming a common problem with Tableau. A lot of what I do involves showing a data point with a comparison of a larger area (district, county or national often) for which I may not have full data. For example comparing local school results to national figures, we have all the raw data for schools in our area, but only have the summary figures for the rest of the country or as with this example, I only have summary stats provided full stop.
I've given up on this example for now but I know there are going to be higher priorities cases where people will want me to be displaying comparison data for it to be useful.
-
3. Re: Add a formula filter to dashboard action filter?
Simon RuncAug 31, 2016 9:52 AM (in response to Adrian Lee)
hi Adrian,
Yes I can see the use-case...so hopefully the second option I provided does the trick?
...However, it looks like you've had to 'hack' together this data together in a (less than ideal) union-type thing, so that Somerset total figure appears as a ward (when it's not...and then causes problems of having to filter it out when you only want ward stuff...etc.). Have you thought about data blending? This way you have one data-source at Ward level, and a second at county and then just blend on month (and maybe County if you have more than one county to look at)?
-
4. Re: Add a formula filter to dashboard action filter?
Dilyana Suleymanova Nov 7, 2016 7:53 AM (in response to Adrian Lee)Hi Adrian,
I would like also let the chart appears, when I click to another chart. What were your filter recruitments?
Thank you in advance!
Kind regards
Dilyana