Set analysis: dates using dates from another table

[sibrulotte]

Hi, 

I have two tables joined by customer ID. One table has requested amount, adn a date of request. The other has actual revenu, and a revenu date.

I do not want to create a master calendar that would mess up the data model and create circular references.

Is there a way to show a table with a date as dimension, sum of requested amount for that day, and sum of actual revenue for that same day.

Requests:

Customer Id	Request date	request amount
1	2020-04-10	10
2	2020-04-10	15
1	2020-04-12	20
3	2020-04-12	30
4	2020-04-12	40
5	2020-04-15	50

Actuals:

Customer ID	revenu date	actual revenu
1	2020-04-12	12
2	2020-04-12	16
1	2020-04-12	21
3	2020-04-12	28
4	2020-04-12	42
5	2020-04-12	46

Resulting graph pivot table would look like this

Date	requested amount	actual amount
2020-04-10	25	0
2020-04-12	90	165
2020-04-15	50	0

I'm thinking I might have to build a table in the script...

[Arthur_Fong]

Try this:

Dimension for pivot table:

Request Date

Expressions:

sum([request amount])

sum (DISTINCT if([Request date]=[revenu date],[actual revenu]))

[sibrulotte]

thx for the try.

Returns 0 for all dates though.

[Arthur_Fong]

I am getting the expected output:

Do check back your Dates field, whether they are in date format.

[sibrulotte]

Check again. I failed to give you correct data to input. Your model does not actually have a join on customer ID since one of the two has small caps for ID...

But if you had a propoer model that was joined, it would return 103 instead of 165. That is the total revenu for customer 1,3 and 4, the only customers to have requests on april 12th.

[Arthur_Fong · Accepted solution]

Try this:

Requests:
load Key,Date,sum([request amount])as [request amount]
group by Key,
		Date; 
load [request amount],[Request date]&'|'&[Customer ID] as Key,[Request date]as Date
 inline [
Customer ID	Request date	request amount
1	2020-04-10	10
2	2020-04-10	15
1	2020-04-12	20
3	2020-04-12	30
4	2020-04-12	40
5	2020-04-15	50
](delimiter is '	'); 
join
load Key,Date,sum([actual revenu])as [actual revenu]
group by Key,
		Date; 
load [actual revenu],[revenu date]&'|'&[Customer ID] as Key,[revenu date]as Date
 inline [
Customer ID	revenu date	actual revenu
1	2020-04-12	12
2	2020-04-12	16
1	2020-04-12	21
3	2020-04-12	28
4	2020-04-12	42
5	2020-04-12	46
](delimiter is '	');

exit script;

Front end table:
           Dimension : Date
           Measure : sum([request amount])
                             : sum([actual revenu])