To GoodData homepage
Solved

How to filter by 2 different dates

  • 14 December 2021
  • 4 replies
  • 193 views
P
  • PD5
  • Known Participant
  • 11 replies

I have a data model with orders and refunds in separate tables. I would like to use MAQL to create a metric showing the sum of all the order totals, less the sum of all the refund totals for a particular date and/or date range.

 

The problem is that the two tables are using different dates. The orders table has “date the order was created” and the refunds table has “date the refund was created”. This is fairly simple in pure SQL using 2 sub-queried, Is there a way of making this work in an MAQL query?

 

Edit: I should also mention that the order refunds table is linked to the orders table via the order id which complicates things further.

 

 

 

icon

Best answer by Joseph Heun 21 January 2022, 09:25

View original

4 replies

Joseph Heun
Rank
Userlevel 3

Unfortunately, in this situation you cannot put these two metrics into a single insight if they do not share the same date dimension. GoodData currently does not support this.

If it is possible, we recommend to change the data model so that you have a shared date dimension for both fact tables. Either connect one of the existing ones to the other table or add a brand new date dimension and connect it to both fact tables.

It works similarly as any other shared dimension. So, each of your fact tables will contain one date which is connected to this shared date. Which date it is depends on to what date you map it to. Also, you should map it to the date by which you want to compare your data.

 

Also for the data modelling topics, feel free to check the following courses in oir free GD university:
https://university.gooddata.com/understanding-logical-data-model
and
https://university.gooddata.com/designing-data-models

Joseph Heun
P

Thanks for your quick reply Joseph. I did think of altering the data model to have the same date dimension used by both tables. I think this could work. Thanks again for your help.

P

Okay I have tried Joseph’s suggestion and unfortunately it does not work as the data model now complains that there are multiple paths to the shared date dimension:

 

refund table → refund created date column → shared date dimension

refund table→ order reference column → order → order created date column → shared date dimension

 

Maybe I’ll create another table for refunds that isn’t linked back to the orders table and use that to link back to the shared date dimension.

Joseph Heun
Rank
Userlevel 3

Yes, multiple paths to the shared date dimension could cause some issues like this. You will need to ensure that there isn’t a triangle or loop in the LDM. You can still manage to report using variations of the By Clause, including Explicit Lifting

Joseph Heun

Reply


Join us on
Welcome to the GoodData Community!Cookie settings