How to do Monthly data with calculations in the table

[Sara_3]

Hi All,

Requirement: I know how to do calculations  but I need help how to input/handle data like monthly associated with (P Release date) and calculate monthly data in the table. (do i need any counter or loop or group by with resident load in script) ? 

I have a huge data (P Release Date) from 2019 to current but here I am using inline table (sample data) to understand the requirements.

Inline Table (data):

main:
load * inline [
P Release Date, Deleted On, PO Release Date
12/21/2022, -, 1/18/2023
12/23/2022, 2/27/2023, -
12/27/2022, -, -
1/4/2023, 1/24/2023, -
1/4/2023, -, 1/17/2023
1/4/2023, -, 2/28/2023
1/4/2023, -, 11/30/2023
1/6/2023, -, 2/9/2023
1/6/2023, -, 2/16/2024
1/11/2023, 5/10/2023, -
1/17/2023, 3/17/2023, -
1/17/2023, -, 4/11/2023
2/2/2023, -, 2/7/2023
2/2/2023, -, 6/21/2023
2/6/2023, 6/6/2023, -
2/15/2023, 9/11/2023, -
2/15/2023, -, 3/14/2024
3/2/2023, -, 5/3/2023
3/2/2023, -, 8/16/2023
3/2/2023, -, 9/22/2023
3/6/2023, -, 1/2/2024
3/30/2023, 4/11/2023, -
3/30/2023, 6/7/2023, -
3/30/2023, 8/7/2023, -
3/30/2023, -, 4/20/2023
3/30/2023, -, 8/29/2023
];

Output Result (in the table):

	Calculation 1	Calculation 2	Calculation 3	Calculation 4	Total of calculation 1,2,3, and 4
Jan 2023	0	1	1	0	2
Feb 2023	1	4	3	0	8
Mar 2023	1	3	4	0	8

Calculated Columns 1, 2. 3, 4, and 5.

Calculation 1 :

P Release Date is before start of month

Deleted On is blank

PO Release Date is blank

Calculation 2 :

P Release Date is before start of month

Deleted On is blank

PO Release date is after start of month

Calculation 3 :

P Release Date is before start of month

Deleted On is after start of month

PO Release Date is blank

Calculation 4 :

P Release Date is before start of month

Deleted On is after start of month

PO Release Date is after start of month

Calculation 5 :

Add 1, 2, 3, and 4.

Thanks in advance.

Sara.

[QFabian]

Hi  @Sara_3 , one option is :

Calcultation:

Load

     monthname([P Release Date]) as Period,

    if([P Release Date] < monthname([P Release Date]) and

    isnull([Deleted On]) or [Deleted On] = '' or [Deleted On] = '-' and 

    isnull([PO Release Date]) or [PO Release Date] = '' or [PO Release Date] = '-', 1, 0) as Calculation_1,

//     [P Release Date]

//    [Deleted On], 

//    [PO Release Date]

Resident main;

[Sara_3]

Thank you so much but the output will be 2 rows of each month and the values are not correct. i have included your given code in my script and i got this:

Period	Calculation_1
Dec 2022	0
Dec 2022	1
Jan 2023	0
Jan 2023	1
Feb 2023	0
Feb 2023	1
Mar 2023	0
Mar 2023	1

My requested output is to calculate the 5 calculations of each month:

	Calculation 1	Calculation 2	Calculation 3	Calculation 4	Total of calculation 1,2,3, and 4
Jan 2023	0	1	1	0	2
Feb 2023	1	4	3	0	8
Mar 2023	1	3	4	0	8

[Kushal_Chawda]

@Sara_3  for Jan 2023 Isn't Calculation 1 should be 1? 

[Sara_3]

Hi Kushal, 

I already have set Analysis for the calculations, but it is ststic for month Jan 2023 and I got the expected outputs. but i am having difficultly to align monthly data in the table like how to do dynamic like i am having data from Jan 2019 till now? Do you know how to do the output data align in the table?

Calculation1 :

Count({$<[P Release Date]={"<=1/1/2023"}, [Del On Flag]={N},[PO LI RelDT Flag]={N}>}[P Release Date])

Calculation 2:

Count({$<[P Release Date]={"<=12/31/2022"}, [Del On Flag]={N},[PO LI Release Date]={">=1/2/2023"}>}[P Release Date])

Calculation 3:

Count({$<[P Release Date]={"<=1/1/2023"}, [Deleted On]={">=1/1/2023"},[PO LI RelDT Flag]={"N"}>}[P Release Date])

Calculation 4:

Count({$<[P Release Date]={"<=1/1/2023"}, [Deleted On]={">=1/1/2023"},[PO LI Release Date]={">=1/1/2023"}>}[P Release Date])