This can’t be as hard as it seems. I have a data cube with transactional level data, example fields: Order#, Location, SalesAmt. I want to show total SalesAmt by Location but I want to have a filter option to exclude orders that have < 100,000 SalesAmt. Any filter I use with SalesAmt only applies to what is visually on the screens after aggregation by Location (ie: doing a SQL having) instead of filtering at the Order level (SQL WHERE). I’d like the solution to be at the metric set or dashboard level so users can set the filters to whatever threshold they want - not have me set it in the data cube. What am I missing here?? Thank you.
Filter on measure (ie: SQL WHERE vs HAVING)
I am not sure what you mean here, what type of VIS are you using here?
If the Measure is on the Actual SalesAmt value (not an aggregation of that value) I would expect it to work. Also remember if you have filtered by location then the salesAmt value filter will be an additional filter not replace the location filter.
Sorry if I said stuff you already know, I just went basic until i get a better understanding on the issue.
Hi Kelly,
From my understanding, you are running into an issue of pre-aggregation vs post-aggregation where your filtering needs to happen before any aggregation on the metric set.
One approach is to create a data cube and use the calculated element to perform a check - pre-aggregation. The code below will create a new “Is Excluded” field in the data that you can have your users filter on with a simple checkbox.
1 Like
Thank you for the answers.
Jeff: Yes, that is the issue I am running into. Your solution will work but I was hoping to do a non-data cube solution so that the users would be free to change the criteria as they worked with the data, excluding orders under 100,000 one day but having the ability to exclude orders under 200,000 the next day if they wanted.
James: I am looking at the data in a table visualization. When you add a filter to the SalesAmt, it filters it post-aggregation. I want a solution to filter it pre-aggregation without having to define the field in my SQL source or in the data cube.
This seems like something that people would use a lot but perhaps everyone defines explicit fields in the data cube as Jeff pointed out.
Hi Kelly,
You can still use my approach of doing this work in the Data Cube and still give the user control over the values that you’re filtering against. Did you know that you can build filters directly into the Data Cube which will be applied before any aggregation you do in the Metric Set?
Below is an example where i create a public parameter that you can use to give the user control. Note: Checking it off as public allows you to hook into this parameter on the dashboard.
Genius!
I didn’t know that this existed but have been wanting it for ages!!
Definitely a tip of the week.
1 Like
I haven’t done many measure value filtering, well just one and it didn’t go well. I am now guessing because it I was not aware that it was doing post aggregation filtering and that not what i would expect it to do.
A better solution is to add an Output Element Filter on the SQL column that is supposed to be filtered.
This filter should be made private or public based on requirements. This way the entire filtering operation is pushed to the provider, so the performance is superior.
Jeff, thank you for the additional option. I added the public parameter but I do not see it as an option to check in the view parameter when I use it in a dashboard and add a range filter. Can you advise to the next step after creating the public parameter? Also, to confirm, this only works with a data cube with no storage type, correct?
Hi Kelly,
Give this a go - i think too many potential options were thrown around (by me).
*edit: new gif, previous one didn’t work
cheers.
Jeff, that worked perfectly, thank you so much!
2 Likes