02-13-2023 12:32 PM
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
02-14-2023 08:44 AM
Why can't you use a range join condition between tables A and B ?
https://docs.incorta.com/cloud/concepts-join-condition
02-14-2023 10:36 AM
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
02-14-2023 10:43 AM
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.
02-17-2023 09:41 AM
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)