Stuck trying to get custom filter to work every day of the week

  • 4 April 2021
  • 2 replies

I have a look that compares the daily revenue of each previous day compared to that day last week. I use the was in the last two weeks filter as well as this custom filter:

mod(diff_days(date(2008,01,01), now()) + 1, 7)>${transactions.transaction_day_of_week_index}

The issue is that on Monday if I run the custom filter I don’t get the perfect 7 day line up for the last two weeks. If however I remove the custom filter and just the standard was in the last two weeks filter the last 7 days line up perfectly. So to combat this I created a whole different dashboard that has this look with no custom filter that is scheduled to run just on Monday morning. What I want is to have code that would work every day even Monday. 

Can someone help write the above custom filter that will basically not use the custom filter if its Monday but do use the above custom filter if its any day but Monday? 


2 replies

Userlevel 5

What if you simply use the “last 14 complete days” as a filter and compare row value with value 7 rows below and show only the first 7 rows in viz?

I was not successful in using the above suggestion. I guess what I am asking is how do I change up the custom filter so that if its Monday it does one comparison and if its any other day than Monday it does another. So far only two different reports have worked for me but I feel like this can be done in custom filter only?

Here are the filters that give me the correct visualization on Monday:


Here is the correct Filter every other day of the week:


The point is to have a bar chart that shows only the sales for days up to that point in the week. The filter that does this on all days but Monday works great but on Monday if you run it you get an extra Monday. So what I am looking for is a single custom filter that works every day of the week and shows the results for up to that day compared to that day last week. So it should only show two weeks worth of data and on Monday have a full Monday - Sunday Line up.