topic Re: Rangesum function in expression to show data in simple table in App Development

Rangesum function in expression to show data in simple table

Sara_3 — Wed, 09 Apr 2025 15:41:59 GMT

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.

Re: Rangesum function in expression to show data in simple table

Chanty4u — Thu, 10 Apr 2025 06:01:15 GMT

Try this

RangeSum(

Above(

Count({

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

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

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

} [Release Date]),

0, RowNo())

)

Re: Rangesum function in expression to show data in simple table

Sara_3 — Thu, 10 Apr 2025 13:43:03 GMT

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,

Re: Rangesum function in expression to show data in simple table

marcus_sommer — Thu, 10 Apr 2025 13:45:43 GMT

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

Re: Rangesum function in expression to show data in simple table

Sara_3 — Thu, 10 Apr 2025 14:09:45 GMT

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.

Re: Rangesum function in expression to show data in simple table

Sara_3 — Thu, 10 Apr 2025 14:14:19 GMT

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]))"}

Re: Rangesum function in expression to show data in simple table

marcus_sommer — Fri, 11 Apr 2025 05:44:25 GMT

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.

Re: Rangesum function in expression to show data in simple table

Sara_3 — Mon, 14 Apr 2025 14:30:15 GMT

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

Re: Rangesum function in expression to show data in simple table

marcus_sommer — Tue, 15 Apr 2025 05:13:33 GMT

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.