Rangesum function in expression to show data in simple table

[Sara_3]

Hi everyone, 

I am working on a project which has multiple date fields and I need to show data in the simple table by Month. 

here is my table:

Main:

Load 1 as rowCount,

Date("Release Date") as "Release Date",

Date(" PO Release Date") as "PO Release Date",

Date(" Finished Date") as "Finished Date",

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

MonthStart([Release Date]) as "MonthStart Date"

------------------------------------------------------------------------

the first requirement is:

Starting Balance

Count of items where:

Release Date is before start of month

Finished Date is blank

PO Release Date is blank

-----------------------------------------------------------------

I have done with this requirement by the following expression and getting the expected result:

RangeSum(Above(count({<[Finished Date Flag]={'N'},[PO Release Date Flag]={'N'}>}[Release Date]),1,RowNo()))+Sum(0) 

The issue is the second requirement where we can analyze the 2 dates' data. the second requirement is :

---------------------------------------------------------------

Second requirement is :

Release Date is before start of month

Finished Date is blank

PO Release date is after start of month

the first and second part is same as the first one expression but how to do the third part where we are looking the after start of month data of PO Release Date

How do I use Rangesum function because we are looking Release Date before data where we are using Above rangesum and PO Release Date is after the month like Below Rangesum.

Can we use both above and below in rangesum expression?

Can we do this requirement in expression?

Thanks in advance,

Sara.

[Chanty4u]

Try this 

RangeSum(

  Above(

    Count({

      < 

        [Finished Date Flag] = {'N'},

        [PO Release Date] = {">=$(=MonthStart([MonthStart Date]))"},

        [Release Date] = {"<$(=MonthStart([MonthStart Date]))"}

      >

    } [Release Date]),

  0, RowNo())

)

[Sara_3]

Thanks for your reply I'm getting all 0 zeros in values when i applied your expression. 

is there any other way to do it?

I really appreciate it,

[marcus_sommer]

I suggest to extend the flag-logic within the data-model. The various date-fields might not only checked on any value else against each other if they are greater/lower and/or their difference and/or the differences of their periods and those ones also checked against the conditions. By using 0/1 flags instead of Y/N flags the various flag-fields might be also evaluated with range/sum/max/avg().

[Sara_3]

Thanks for the suggestion i have changed the flag Y/N to 0/1 but same getting the 0 values. i see there are no values in the MonthStart Date fields in the expression.

[Sara_3]

if I put static dates the expression is working fine and getting the expected result so i think the issue is in the following expression :

 [PO Release Date] = {">=$(=MonthStart([MonthStart Date]))"},

        [Release Date] = {"<$(=MonthStart([MonthStart Date]))"}

[marcus_sommer]

Make sure that all single parts of your logic are working, from the date() + monthstart() in the script as well as all of them and the others in the UI objects. Further the $-sign expansion may need quotes to be considered as date, like: {">='$(=MonthStart([MonthStart Date]))'"}

Beside this none of such things should be done within the UI else it belonged to the data-model.

[Sara_3]

Hi,

this is my sample data , do you know how can we do the requirement 2 in the backend / script?

main:
Load
1 as rowCount,
if(date(Date#("Deleted On", 'MM/DD/YYYY')) > 0 ,'Y','N') as "Finished Date Flag",
if(date(Date#("PO Release Date",'MM/DD/YYYY')) > 0 ,'Y','N') as "PO Release Date Flag",
Date(MonthStart(Date#("P Release Date", 'MM/DD/YYYY')), 'MMM YYYY') as [MonthStart Date],
Date(Date#([P Release Date], 'M/D/YYYY h:mm'), 'MM/DD/YYYY') as [Release Date],
Date(Date#([Deleted On], 'MM/DD/YYYY')) as [Finished Date],
Date(Date#([PO Release Date], 'MM/DD/YYYY')) as [PO Release Date];

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

Thanks,

Sara

[marcus_sommer]

The logical requirement isn't really clear but it seems that everything is there and needs just be queried - which may be done in n preceding steps to simplify the approach.

In this regard make sure that the date-fields are natively (with appropriate set interpretation variables) recognized as dates to avoid the date#() and date() stuff. Further the value-exists check may be applied per: sign(len(trim(DateField))). In a similar way might be also the other checks performed, like: sign(Date1 - Date2) whereby the date-fields might be wrapped with the needed monthstart() or monthend() to be suitable with the requirement.