To GoodData homepage
Solved

Filter by date in MAQL while ignoring dashboard filter

  • 29 August 2022
  • 3 replies
  • 101 views
romanzdk

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?

icon

Best answer by Moises Morales 29 August 2022, 16:12

View original

3 replies

Moises Morales
Rank
Userlevel 3

Hi Roman, 

Have you tried using FOR Previous instead? 

The syntax would look like this: 

SELECT metric FOR Previous(time_attribute, #periods_ago)

Regards, 

 

-Moises

 

 

 

romanzdk

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 :(

Moises Morales
Rank
Userlevel 3

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:

SELECT MAX(Date) BY ALL OTHER

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:

SELECT Metric WHERE Date >(SELECT Max_Metric - 55 BY ALL OTHER WHERE(SELECT Metric BY Date)>0)

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.

 

-Moises

Reply


Join us on
Welcome to the GoodData Community!Cookie settings