I have the following problem: I want to schedule a Look on daily basis. It should be a visualisation (single value) which is based on a Yes/No variable per day, where the YES/NO variable is dependent on a measure. This measure is shown as a comparison value. Example of the data:
Date | Completion Rate | Is there an issue? 05-01-2017 | 100%| NO 04-01-2017 | 70% | YES 03-01-2017 | 50% | YES 02-01-2017 | 90% | NO 01-01-2017 | 80% | NO
Example of the viz:
NO 90% completion rate
What I want to do:
I want to send out this Look (the single value) ONLY when Is there an issue? is equal to YES. When this is not the case, I don’t want anything to be sent out.
What I tried:
I tried only displaying the first row of the data (hiding all others) and then the viz only changes when a new day with YES is entered (hide “No’s” from viz is enabled). However, the data/results do change every day (even though the viz does not change on a daily basis), and hence, it will send out a new e-mail everyday, even though Is there an issue? for the latest day is NO.
Does anyone have an idea how to solve this problem? If I need to provide more information, please let me know.
Just a thought
@Tomas - if all you care about is the first row, why not change the row limit to 1? That way you can just filter on “is there an issue?” being YES.
The actual situation is a little bit more complex, I’ve got a table calculation which depends on row number 7, 14, 21 and 28, hence I do need multiple rows of data.
The only two ways to control when results get sent out in Looker are:
Since the results change every day, we can rule out option 1.
The only way I see this working is with option 2. The trick is, “Hide No’s from viz” doesn’t actually affect whether or not there are results. So you’d need a way to filter the result set such that there is actually no data in the data section when Is there an issue? is NO. You might try playing with custom filters to do this, but since custom filters don’t have
offset, this might be a challenge.
This would be a lot easier if we could filter by table calculations, or use the full range of table calculations in custom filters. Same request, really. I’ll let the product team know you’d like this - I know the reason we don’t have it currently is because functions like
offsetdon’t translate well to SQL and are instead done on the front-end.
Thanks Sam for the clarifying explanation. Is there any topic where I can track what the Product team is working on/what’s in the pipeline?
And furthermore, do you have any documentation on the custom filters, if it indeed is possible to solve the problem using these filters?
With respect to documentation, the intro to custom filters is the place to start. From there, you can find the accepted operators and see if you can rewrite your problem in terms of these filters.