Forum Discussion
Join Tables on Dates
I have 2 reports that I pull data out of. After a boat load of edit query steps I get to the below tables. My issue is that after when I setup a display table with the Table1 dates the Table2 Values only produce a max on each line. When I setup a display table on the Table2 dates the Table1 Values only produce a max on each line.
Is there a way to get the dates to talk to each other without having to append the queries? I hope that was clear.
Table1:
| ReportDate | Value |
| 1/1/2023 | 100 |
| 1/1/2023 | 199 |
| 1/1/2023 | 109 |
| ... | ... |
| 12/1/2023 | 79 |
| 12/1/2023 | 200 |
Table2:
| ReportDate | Value |
| 1/1/2023 | 150 |
| 1/1/2023 | 110 |
| 1/1/2023 | 101 |
| 1/1/2023 | 100 |
| ... | ... |
| 12/1/2023 | 400 |
Result (table2 values only give the full value on each month not the monthly value):
| Table1Month | Table1Values | Table2Values |
| January | 12,000,159 | 109,475,195 |
| Febuary | 10,758,912 | 109,475,195 |
| March | 13,000,785 | 109,475,195 |
| ... |
Hello ronnie_roberts ,
You could create a date table that displays all the dates, and make a relationship from this table through the date column to the two table by each date column.
then display the data with the date that you get from the new created date table.
If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍
2 Replies
Hello ronnie_roberts ,
You could create a date table that displays all the dates, and make a relationship from this table through the date column to the two table by each date column.
then display the data with the date that you get from the new created date table.
If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍
Thank you! I didn't realize you could join on dates!
