Sum based on Date (Monthly) in Set Analysis in Table

[Sara_3]

Hi everybody!

In request of a customer, I'm trying to make a table that lists a Month Field and the calculation. I have done the calculation in table with the static Date. 

please help me to show them in table by Month like Jan 2023 , Feb 2023, and so on.

I have data from Jan 2019 to present but here i am created inline sample table.

i have created in load statement

1 as rowCount,

if(date("Deleted On") > 0 ,'Y','N') as "Del On Flag",

if(date("PO Release Date") > 0 ,'Y','N') as "PO LI RelDT Flag",

Calculation :

P Release Date is before start of month (for ex: March 2023 , we will have all data before this month ) where

Deleted On is blank

PO Release Date is blank

Calculation with static date: =SUM({<[P Release Date]={"<=01/01/2023"}>*<[Del On Flag]={"N"}>*<[PO  RelDT Flag]={"N"}>} [rowCount])

Consider the following example 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
];

expected data in the table :

MonthStart(P Release Date)  ------ Calculation1 

12/1/2022-------------------------- 0

1/1/2023-------------------------    0

2/1/2023-------------------------    1

3/1/2023--------------------------- 1

Thanks in advance and kind regards,

Sara.

[JandreKillianRIC]

Hi @Sara_3 

I dont know if you made a typo, If I look at your sample data there is only 1 record that fit the requirement. 

This one 12/27/2022 as this is the only date where the Delete and PO Date is blank... 

Correct me if I am wrong. 

But based on the initial request. 

Measure: 

Count( {<[PO LI RelDT Flag] = {'N'}, [Del On Flag] = {'N'} >} rowCount)

Script:

main:
Load 
	*, 
    1 as rowCount,
    if(date(Date#("Deleted On", 'MM/DD/YYYY')) > 0 ,'Y','N') as "Del On Flag",
    if(date(Date#("PO Release Date",'MM/DD/YYYY')) > 0 ,'Y','N') as "PO LI RelDT Flag",
    Date(MonthStart(Date#("P Release Date", 'MM/DD/YYYY')), 'MMM YYYY') as MonthStart;
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
];

Result Table: 

Regards - Jandre

[Sara_3]

Hi Jandre,

Thank you so much , there is no typo yes there is only 1 record we are have both null values. 

I need 2 columns Date and calculation. I need all 4 monthStart (P Release Date) like (dec 2022, Jan 2023, Feb 2023, Mar 2023) associated with calculation. 

Your Dec 2022 calculation is not correct because the calculation logic is : 

(for ex: Dec 2022 , we will have all data before this month ) where Deleted on and PO Release Date is Null. we dont have data before Dec 2022 so we will get 0 (zero ) in the column.

Calculation :

P Release Date is before start of month 

Deleted On is blank

PO Release Date is blank

Calculation with static date: =SUM({<[P Release Date]={"<=12/01/2022"}>*<[Del On Flag]={"N"}>*<[PO  RelDT Flag]={"N"}>} [rowCount])

expected table :

MonthStart (P Release Date)	calculation
12/1/2022	0
1/1/2023	1
2/1/2023	1
3/1/2023	1

the main problem is how to do the calculation dynamically for each month and current month we are having past months data too. Either in the script or in the set  Analysis.

Please let me know if you have any solution. i have tried multiple things but did not get the expected data in the table.

Thanks, 

Sara.

[JandreKillianRIC · Accepted solution]

Hi @Sara_3 

Measure is just Max (Flag) Dimension: MonthStart

Script:

main:
Load 
    1 as rowCount,
    if(date(Date#("Deleted On", 'MM/DD/YYYY')) > 0 ,'Y','N') as "Del On Flag",
    if(date(Date#("PO Release Date",'MM/DD/YYYY')) > 0 ,'Y','N') as "PO LI RelDT Flag",
    Date(MonthStart(Date#("P Release Date", 'MM/DD/YYYY')), 'MMM YYYY') as MonthStart,
    Date(Date#([P Release Date], 'MM/DD/YYYY')) as [P Release Date],
    Date(Date#([Deleted On], 'MM/DD/YYYY')) as [Deleted On],
    Date(Date#([PO Release Date], 'MM/DD/YYYY')) as [PO Release Date];
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
];

NewMain:
Load 
	*,
    IF(Peek('Flag') >= 1, 
    Peek('Flag'), 0) +
    	IF([P Release Date] > Previous(MonthStart) and Previous([Del On Flag]) = 'N' and Previous([PO LI RelDT Flag]) = 'N', 1, 0) as Flag
Resident main
Order By [P Release Date];

Drop Table main;

Regards - Jandre

[Sara_3]

Hi Jandre, 

sorry for the late reply, i was sick.Thank you so much , i have included your script in my actual data and I got it correct. I have a customer requirement that have 3 more calculations.

Can we add 3 more calculation in the same script? 

here are the 3 calculations and these are the month of Jan 2023 :

Calculation 2 :

P Release Date is before start of month 

Deleted On is blank

PO Release date is after start of month 

Calculation in KPI: Count({$<[P Release Date]={"<=12/31/2022"}, [Deleted On ]={-},[PO  Release Date]={">=1/2/2023"}>}[P Release Date])

Calculation 3 :

P Release Date is before start of month

Deleted On is after start of month

PO Release Date is blank

Calculation in KPI: Count({$<[P Release Date]={"<=1/1/2023"}, [Deleted On]={">=1/1/2023"},[PO Release Date]={"-"}>}[P Release Date])

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 in KPI: Count({$<[P Release Date]={"<=1/1/2023"}, [Deleted On]={">=1/1/2023"},[PO  Release Date]={">=1/1/2023"}>}[P Release Date])

Thanks again,

Sara.