cancel
Showing results for 
Search instead for 
Did you mean: 

Calculation based on Custom calendar and non joined tables

msinha8
Ranger

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
DATEAA_MANHOUR DATENET_COST
1/1/202310 1/1/2023100
1/2/202315 1/2/2023200
1/3/202312 1/3/2023120
1/4/202313 1/4/2023140
1/5/202310 1/5/2023150
1/6/202312 1/6/2023130
1/7/202320 1/7/2023110
1/8/202315 1/8/2023100
1/9/202316 1/9/2023130
1/10/202319 1/10/2023150
1/11/202320 1/11/2023160
1/12/202314 1/12/2023170
1/13/202312 1/13/2023150
1/14/202319 1/14/2023140
1/15/202312 1/15/2023120
1/16/202313 1/16/2023130
1/17/202314 1/17/2023170
1/18/202316 1/18/2023180
1/19/202319 1/19/2023190
1/20/202313 1/20/2023100
1/21/202317 1/21/2023150
1/22/202310 1/22/2023190
1/23/202311 1/23/2023100
1/24/202314 1/24/2023200
1/25/202314 1/25/2023150
1/26/202317 1/26/2023160
1/27/202319 1/27/2023170
1/28/202320 1/28/2023180
1/29/202321 1/29/2023190
1/30/202320 1/30/2023200
1/31/202324 1/31/2023210
2/1/202324 2/1/2023220
2/2/202325 2/2/2023230
2/3/202326 2/3/2023200
2/4/202328 2/4/2023190
2/5/202320 2/5/2023160
2/6/202321 2/6/2023170
2/7/202322 2/7/2023180
2/8/202323 2/8/2023190
2/9/202324 2/9/2023200
2/10/202325 2/10/2023200
2/11/202320 2/11/2023100
2/12/202325 2/12/2023150
2/13/202320 2/13/2023200
2/14/202325 2/14/2023100
2/15/202315 2/15/2023150
2/16/202310 2/16/2023100
2/17/202315 2/17/2023150
2/18/202310 2/18/2023100
2/19/202320 2/19/2023200
2/20/202325 2/20/2023150
2/21/202330 2/21/2023100
2/22/202335 2/22/2023200
2/23/202340 2/23/2023150
2/24/202345 2/24/2023100
2/25/202350 2/25/2023100
2/26/202310 2/26/2023150
2/27/202315 2/27/2023200
2/28/202320 2/28/2023150
3/1/202325 3/1/2023110
3/2/202310 3/2/2023100
3/3/202315 3/3/2023150
3/4/202320 3/4/2023100
3/5/202330 3/5/2023200
3/6/202340 3/6/2023250
3/7/202315 3/7/2023100
3/8/202310 3/8/2023120
3/9/202320 3/9/2023130
3/10/202325 3/10/2023140

 

Custom Calendar
MonthCycle Start DateCycle End Date
January1/3/20232/1/2023
February2/2/20233/1/2023
March3/2/20234/3/2023
April4/4/20235/1/2023
May5/2/20236/1/2023
June6/2/20237/3/2023
July7/4/20238/1/2023
August8/2/20239/1/2023
September9/2/202310/2/2023
October10/3/202311/1/2023
November11/2/202312/1/2023
December12/2/20231/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 NameJanuary
YearNet CostDMHNet Cost/DMH
2023$4,660481$9.69

For the month of Mar-23 :

Month NameJanuaryFebruary
YearNet CostDMHNet Cost/DMHNet CostDMHNet Cost/DMH
2023$4,960544$9.12$4,380668$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

 

 

4 REPLIES 4

RADSr
Captain
Captain

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

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

-- IncortaOne@PMsquare.com --

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

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.  

 

-- IncortaOne@PMsquare.com --

msinha8
Ranger

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)