Hi everyone! I have a date filter on my entire table that pulls data from the last week (7 days). I have measures such as revenue, clicks, conversions, traffic quality score, etc.
I want only one of my measures (Traffic Quality Score) to be filtered so that it only shows data from one day of the week instead of all 7 days. Is this possible without LookML? Thank you!
Hi @tiff1,
Did you try creating a custom measure on the front end in the explore and add a date filter in the custom measure? You could hard code the date like the example below or use a relative filter based on the date difference between now and the day you want to show or something similar depending on the condition.
Or, do you want to show a constant number to compare the date from one day to the other days show in the explore?
Relative Method:
Please let me know if you have any questions!
Thanks,
Eric
Hi @Eric_Lyons ! Thanks for getting back to me. I tried creating a custom measure but got this error that “filter expression is not supported for measures of non-aggregate type ‘number’ “
Here is the filter I tried to apply. I want this filter to show the Traffic Quality Score only on 1/1/2021 if the clicks are greater than or equal to 700.
Am I doing something wrong? The help is greatly appreciated!
Hi @tiff1
For a filtered measure even on the front-end, we cannot use a type: number. There needs to be some form of aggregation on the measure like a count, sum, etc. If there are individual line items for each day, you potentially try to use type: max or type: average. The average or sum of a single value’s result is just the original value. If you cannot change this since you mentioned you do not want to do this in Lookml. You potentially could use table calculations.
Thanks,
Eric
@Eric_Lyons Can you provide an example of how I would make this into a table calculation? If I use sum or average it takes the column’s sum or average not the individual rows across.
If I were to do this in LookML what would be the steps? I currently don’t have LookML access but can make a case for it with this problem.