Dynamically Setting Initial Filters in Einstein Analytics

An Einstein Analytics dashboard can be a powerhouse of information. Displayed via various aesthetically appealing charts, KPIs drive the point home with tables giving access to immediate data contributing to these KPIs, each piece of information being extremely valuable to the end-users when done in the right way. However, this involves meticulous planning on the developer’s end, including gathering all the information, building the right story, and finally putting them all together and doing what we do best – waving our saql wands, practicing magic in the form of dashboards. But, a big part of our time gets allocated to maintenance work. Some work we absolutely can’t escape, but there is some maintenance work we are be able to automate. After all, automation is what removes the redundancy of repeated acts and let’s us concentrate on solving the bigger challenges.

One of the things we can automate is the initial value for listselectors. Almost every dashboard I have worked on had multiple filters on the top, and many times we want to select some initial values for those filters. This enables the viewer to have a refined view of the dashboard when they open it. We all know we don’t want to be aggregating the cumulative Pipeline value in our dataset over all months in our Month filter, and then show it on the dashboard. Instead we would want to show the Pipeline value for just the current month, enabling the user with the option to change the month and get the correct numbers for any other month. This is easily achievable by using the ‘Pick Initial Filters’ feature in EA and setting the Month filter to current month. But who is going to take the responsibility to go in on every month’s first, bright and early, and change the initial value to the new month? What happens when the audience for the dashboard is across different time zones, or even across different continents? Where do we place this responsible person so that we can be sure whoever logs in on 1st June will see the right data on their dashboard, instead of getting annoyed at it, causing them to ignore the dashboard for the next few days, or even weeks?

The answer is by dynamically selecting the start value for our filters. Where we had earlier hardcoded the initial value as ‘May’ (05) for the ‘start’ parameter in our Month Selector step and hardcoded ‘2018’ as the ‘start’ value for our Year Selector step, we will replace them with bindings which will dynamically update the Year and Month value selected in the filter.

Let’s have a look at the following dashboard snippet:

 

The initial values for the filters on top are being calculated in another step, Current_Yr_Month which uses the now() function to get the Current_Year and Current_Month and then bound using a results binding

Things to remember before we proceed to adding bindings for our ‘start’ values:

  • Start values can be dynamic only for type ‘SAQL’, ‘SOQL’, ‘Static Flex’ steps – Convert your current filter step into one of these types. This can be easily done by going to the query editor for the step and making minimal changes to the query. I like changing the limit from 2000 to 2005 – no harm done and my step now becomes type saql.
  • Turn facet off for the intermediary step – Turn the ‘receive’ as well as ‘broadcast’ facet off for the step which we are going to use for the results binding. Otherwise, it will become a sort of loop, causing this step to cancel each filter out, which is not our intention. As a reminder, our intention is to select Initial Values for the Filters on the top – not to filter them out to show only specific values.

Now that we have our steps set up correctly, the only remaining task is to add the binding to the ‘start’ parameter:

1. “Year_Selector_1”: {
2. “broadcastFacet”: true,
3. “groups”: [],
4. “label”: “Year Selector”,
5. “numbers”: [],
6. “query”: “q = load \”Opportunities\”;\nq = group q by ‘CloseDate_Year’;\nq =foreach              q generate ‘CloseDate_Year’ as ‘CloseDate_Year’, count() as ‘count’;\nq =              order q by’CloseDate_Year’ asc;\nq = limit q 2005;”,
7. “receiveFacet”: true,
8. “selectMode”: “singlerequired”,
9. “start”: “{{cell(Current_Yr_Month.result,0,\”Current_Year\”).asObject()}}”,
10. “strings”: [],
11. “type”: “saql”,
12. “useGlobal”: true,
13. “visualizationParameters”: {}
14. }

This will take the Current_Year value generated in the step ‘Current_Yr_Month’ and use it as the ‘start’ value for the Year_Selector filter on the top. Similarly, we can use the following binding for the Month_Selector step to get the current Month as the ‘start’ value dynamically:

“start”: “{{cell(Current_Yr_Month.result,0,\”Current_Month\”).asObject()}}”,

Another great use case is when we want to default the dashboard to show the Logged-in users’ data on the dashboard when they start, but with the ability and option to look at other people’s data as well. This kind of situation arises when someone is part of an Account Team where each Account Team Member has access to not only their own data, but also that of their team members. As a part of the Summer17 release we got the ability to query data from the Objects directly using SOQL and we have been using it quite often to get the Logged-in users Id, Name, Role etc and filtering the dashboard for them. But now instead of filtering it out, we are going to set the Account Team Member filter on the top of the dashboard to the Logged-in user.

Eg: When Amada Loud logs in, the filter will default to her but she can go and select any of the other team members from the drop down. Using a SOQL intermediary step, UserData to get the logged-in user details, we can use the following binding to dynamically set the ‘start’ value of the Account Team Member filter:

             “start”: “{{cell(UserData.result,0,\”Name\”).asObject()}}”,  

Though this was all about using dynamic start values for filters on a dashboard we can use it in other places, eg: to highlight the logged-in user’s country on a world map chart, defaulting the dashboard to a Manager vs Rep view using the logged-in users Role information etc. Ending this maintenance of a dashboard is always hard, especially when it gets handed down from one developer to another. So why not make it a little bit easier and automate the small things, whenever possible?

Appendix:

  1. Code for Current_Yr_Month step:

1. “Current_Yr_Month”: {
2. “broadcastFacet”: false,
3. “groups”: [],
4. “label”: “Current_Yr_Month”,
5. “numbers”: [],
6. “query”: “q = load \”Opportunities\”;\nq = group q by all;\nq = foreach q generate toString(now(),\”yyyy\”) as ‘Current_Year’, toString(now(),\”MM\”) as ‘Current_Month’;”,
7. “receiveFacet”: false,
8. “selectMode”: “single”,
9. “strings”: [],
10. “type”: “saql”,
11. “useGlobal”: true,
12. “visualizationParameters”: {}
13. },

2. Code for Month_Selector step:

1. “Month_Selector_1”: {
2. “broadcastFacet”: true,
3. “groups”: [],
4. “label”: “Month Selector”,
5. “numbers”: [],
6. “query”: “q = load \”Oil_Gas_Opportunities\”;\nq = group q by ‘CloseDate_Month’;\nq = foreach q generate ‘CloseDate_Month’ as ‘CloseDate_Month’, count() as ‘count’;\nq =
order q by ‘CloseDate_Month’ asc;\nq = limit q 2005;”,
7. “receiveFacet”: true,
8. “selectMode”: “single”,
9. “start”: “{{cell(Current_Yr_Month.result,0,\”Current_Month\”).asObject()}}”,
10. “strings”: [],
11. “type”: “saql”,
11. “useGlobal”: true,
12. “visualizationParameters”: {}
13. },

3. Code for UserData step:

1. “UserData”: {
2. “groups”: [],
3. “numbers”: [],
4. “query”: “SELECT Id, Name, Role FROM User WHERE Id = ‘!{User.Id}'”,
5. “selectMode”: “single”,
6. “strings”: [
7. “Id”,
8. “Name”,
9. “Role”
10. ],
11. “type”: “soql”
12. },