topic Re: sum value for each Year-Month combination in App Development

sum value for each Year-Month combination

minajalalian90 — Sat, 19 Apr 2025 12:56:52 GMT

Hi,

I want to sum inventory based on the minimum and maximum dates within each Year-Month combination, I need to conditionally sum the inventory for only the rows corresponding to the minimum and maximum dates.

the final result must be like the attachment.

can anyone helps me how to write the expressions?

Please help me in solving this issue.

Re: sum value for each Year-Month combination

rubenmarin — Sun, 20 Apr 2025 07:23:45 GMT

Hi, you can add some fields to the script to have an easier expression with better performance.

In the table where Date is located you can add:

- If(Day(Date)=1,1,0) as isFirstDayOfMonth

- If(Date=Date(Floor(MonthEnd(Date))),1,0) as isLastDayOfMonth

With this 2 fields the expressions could be just:

- Sum({<isFirstDayOfMonth={1}>} Inventory)

- Sum({<isLastDayOfMonth={1}>} Inventory)

Re: sum value for each Year-Month combination

rubenmarin — Sun, 20 Apr 2025 07:28:21 GMT

Or, if you dont' have all the dates, you can also do a join using the dates you have.

In example, if the inventory table has something like

InventoryTable: LOAD Date, Month, Year, Product, Inventory, ... From...

You can add the fields as:

Left Join (InventoryTable) LOAD Month, Year, Product, Date(Min(Date)) as isFirstDayOfMonth Resident InventoryTable Group by Month, Year, Product;