What I would like to achieve:
Select specific day (date) using dashboard filter and from this specific date take e.g. 55 days back and for this period compute e.g. sum of Sales.
Is it possible to somehow achieve this?
I defined currently selected date as
SELECT MAX(Date (Date)) BY ALL OTHER WITHOUT PF EXCEPT Date (Date)
and lagged Day as
SELECT Selected day - 55
and the metric for the sum:
SELECT SUM(Sales) WHERE Date (Date) > Lagged day and Date (Date) <= Selected day WITHOUT PF
however this does not give any results.
How to solve this, please?
Best answer by Moises MoralesView original
Have you tried using FOR Previous instead?
The syntax would look like this:
Unfortunately this still selects only sum for the filtered period (e.g. if I select 5.5.2022 in the filter I get a sum for the 5.5.2022, not for the period 5.5.2022 - 5.4.2022 :(
I was playing around with this a bit and I think I got it. First, we have to create a metric that will take the MAX date for whichever date was selected from the filter:
Then, we have to use this metric and find 55 days prior the MAX date and give us the metric data for all the days greater than that date:
Note that while this is a floating period, meaning that the data will change in time accordingly, you still need to specify the period in the metric and the date filter, and it cannot be taken dynamically from the date filter in the dashboard.