Union of two date range in the dimension

[nelgab]

It seems like a simple problem, but I'm stuck: I have two data tables (A: Income B: Expenses). Both contain 2 columns: Income table: InvoiceDeliveryDate(A); LineNetAmount(A), Expense table: InvoiceDeliveryDate(B); LineNetAmount(B). There are dates when there is no expense but there is income, and vice versa. And there are dates when there is both income and expense in the table. I would like to make a simple single table with the first column containing all the dates, a separate column for Income (where there was no value there should be "0"). Column 3 contains Expenses (where there was no value there should be "0"). Pre-combining the two tables into one table is not a solution.

[sam8]

This should be possible! You can make a blend of these two tables, joining on the date field, and then choosing "full outer join" to get the empty values you're looking for on the income and expense fields.

[cloute]

Hello, sorry for waking this up but I have the same issue. 

I tried doing the full outer join, but problem is, in my graphs, when i have to pick a dimension, i have to either pick DateExpense or DateIncome. So it won't display my overall date, both expense or income, even if i don't always have both data on the same date.

I tried to do it with a calculated field but it appears that this doesn't work on dates. How can i get all my dates in a field, that I can then add as a dimension ? 

Thank you for your help,

[sam8]

I see. Let's try another approach. Try creating a third table that has all of the possible dates. You could create that in a spreadsheet or a database, it doesn't really matter.

Then, start your blend from the table of dates. Join in the expense table and the income table to the date table. Left joins should be fine since all of the dates exist in the date table now.

[Mehdi_Oudjida]

Hey @nelgab 

mimicking a UNION ALL with the article below should solve your problem, directly in Looker Studio. The key point is in your case the usage of COALESCE function with your 2 dates.

https://www.withlookerstudio.com/blog/data-sources/20220222-how-to-mimic-union-all-operator-with-goo...

I hope it helps.

Mehdi