cancel
Showing results for
Did you mean:

## Calculation based on Custom calendar and non joined tables

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 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

4 REPLIES 4
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

Captain

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 --
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)