Need Custom Filter on Measure

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! 

0 4 6,125
4 REPLIES 4

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?
 

12d15a69-14b1-4545-98fd-c6963c519043.png
541e4fef-1efb-4b9f-b7ca-99172c02b111.png


Relative Method:

15d44c14-ef2b-4c8d-953d-c3243db6a88d.png
a4a2583d-2834-4eee-a29e-a45b3f8f3f62.png

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’ “

5796085d-8ed0-4da2-b5ab-c919d264b0a1.png

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. 

aaec7920-d812-471f-a680-74db45a63541.png

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. 

Top Labels in this Space
Top Solution Authors