Calculation based on Custom calendar and non joined tables

[msinha8]

Hi Gurus,

I need some pointer to complete analysis for below requirement .

Requirement:

We need to create a monthly trending report which shows Last month's data, adding upto 12 months. i.e.

Just say for Year 2023, the report should show ;

In the month of Jan-23 : data of Jan-22 to Dec-22
In the month of Feb-23 : data of Jan-23
In the month of Mar-23 : data of Jan-23 and Feb-23
In the month of Apr-23 : data of Jan-23, Feb-23 and Mar-23
..
..
In the month of Dec-23 : data of Jan-23,Feb-23,Mar-23,...., and Nov-23

Metrics used in the report;

a) Net Cost : transactions happened on a particular month ( month is calculated based on external calendar (custom, mentioned below)
b) Man Hour : No. of Man hours required to complete a Job (for example, for the month of Jan-23, Total Number of Man hours to complete all the Job started from 01/01/2023 till 01/31/2023; We have a direct DB column capturing this value (the DB table is joined with Standard Calendar table to calculate Man hours).

Table A		Table B
DATE	AA_MANHOUR		DATE	NET_COST
1/1/2023	10		1/1/2023	100
1/2/2023	15		1/2/2023	200
1/3/2023	12		1/3/2023	120
1/4/2023	13		1/4/2023	140
1/5/2023	10		1/5/2023	150
1/6/2023	12		1/6/2023	130
1/7/2023	20		1/7/2023	110
1/8/2023	15		1/8/2023	100
1/9/2023	16		1/9/2023	130
1/10/2023	19		1/10/2023	150
1/11/2023	20		1/11/2023	160
1/12/2023	14		1/12/2023	170
1/13/2023	12		1/13/2023	150
1/14/2023	19		1/14/2023	140
1/15/2023	12		1/15/2023	120
1/16/2023	13		1/16/2023	130
1/17/2023	14		1/17/2023	170
1/18/2023	16		1/18/2023	180
1/19/2023	19		1/19/2023	190
1/20/2023	13		1/20/2023	100
1/21/2023	17		1/21/2023	150
1/22/2023	10		1/22/2023	190
1/23/2023	11		1/23/2023	100
1/24/2023	14		1/24/2023	200
1/25/2023	14		1/25/2023	150
1/26/2023	17		1/26/2023	160
1/27/2023	19		1/27/2023	170
1/28/2023	20		1/28/2023	180
1/29/2023	21		1/29/2023	190
1/30/2023	20		1/30/2023	200
1/31/2023	24		1/31/2023	210
2/1/2023	24		2/1/2023	220
2/2/2023	25		2/2/2023	230
2/3/2023	26		2/3/2023	200
2/4/2023	28		2/4/2023	190
2/5/2023	20		2/5/2023	160
2/6/2023	21		2/6/2023	170
2/7/2023	22		2/7/2023	180
2/8/2023	23		2/8/2023	190
2/9/2023	24		2/9/2023	200
2/10/2023	25		2/10/2023	200
2/11/2023	20		2/11/2023	100
2/12/2023	25		2/12/2023	150
2/13/2023	20		2/13/2023	200
2/14/2023	25		2/14/2023	100
2/15/2023	15		2/15/2023	150
2/16/2023	10		2/16/2023	100
2/17/2023	15		2/17/2023	150
2/18/2023	10		2/18/2023	100
2/19/2023	20		2/19/2023	200
2/20/2023	25		2/20/2023	150
2/21/2023	30		2/21/2023	100
2/22/2023	35		2/22/2023	200
2/23/2023	40		2/23/2023	150
2/24/2023	45		2/24/2023	100
2/25/2023	50		2/25/2023	100
2/26/2023	10		2/26/2023	150
2/27/2023	15		2/27/2023	200
2/28/2023	20		2/28/2023	150
3/1/2023	25		3/1/2023	110
3/2/2023	10		3/2/2023	100
3/3/2023	15		3/3/2023	150
3/4/2023	20		3/4/2023	100
3/5/2023	30		3/5/2023	200
3/6/2023	40		3/6/2023	250
3/7/2023	15		3/7/2023	100
3/8/2023	10		3/8/2023	120
3/9/2023	20		3/9/2023	130
3/10/2023	25		3/10/2023	140

Custom Calendar
Month	Cycle Start Date	Cycle End Date
January	1/3/2023	2/1/2023
February	2/2/2023	3/1/2023
March	3/2/2023	4/3/2023
April	4/4/2023	5/1/2023
May	5/2/2023	6/1/2023
June	6/2/2023	7/3/2023
July	7/4/2023	8/1/2023
August	8/2/2023	9/1/2023
September	9/2/2023	10/2/2023
October	10/3/2023	11/1/2023
November	11/2/2023	12/1/2023
December	12/2/2023	1/1/2024

Required Report output (Pivot Table):

Row : Year 
Column Name : Month Name
Measure :
                 a) Net Cost
                 b) DMH
                 c) Net Cost/DMH

For the month of Feb-23 :

Month Name	January
Year	Net Cost	DMH	Net Cost/DMH
2023	$4,660	481	$9.69

For the month of Mar-23 :

Month Name	January	February
Year	Net Cost	DMH	Net Cost/DMH	Net Cost	DMH	Net Cost/DMH
2023	$4,960	544	$9.12	$4,380	668	$6.56

Net Cost is calculated based on Custom calendar (For Feb-23, Net Cost is calculated as sum of column NET_COST (table B) for the dates between 01/03/2023 and 02/01/2023 (including both dates) and for Mar-23 , Net Cost is calculated as sum of column NET_COST (table B) for the dates between 02/02/2023 and 03/01/2023 (including both dates)

Note:  There is no joins available between Table A and Table B.

Please let me know, if you need any information.

P.S : I am facing issue while combining metrics and both column using different calendars to calculate months

Regards,

Mritunjay Sinha

[RADSr]

Why can't you use a range join condition between tables A and B ? 

https://docs.incorta.com/cloud/concepts-join-condition

[msinha8]

Thanks @RADSr for the feedback but I can't use the join between Table A and Table B as both tables is joined individually with 2 different calendar table ( table A is joined with Standard Calendar table  whereas table B is joined with Custom Calendar table).

Let me know, if more clarity is required.

Regards,

Mritunjay Sinha

[RADSr]

How about creating an MV master calendar table w/ calendar_date as the key, calendar_month, custom_calendar_month, etc as the attributes. 

That way any given transaction date ( actual or first of month/ last of month ) can roll up into either a calendar or custom month -- you can use CASE logic to present and calculate based on the dates for either grouping.  

[msinha8]

Hi @RADSr , after creating the MV table, the dates which were not overlapping (i.e for the given example, For the month of Feb, date 01/01/2023, 01/02/2023 is missing if I use standard calendar month name (for calculating Net Cost) and Date 02/01/2023 is missing, if I use the custom calendar's month name (for calculating DMH).

Or am I missing something here?

(I am attaching the MV query, which I used to create Calendar table)