sum value for each Year-Month combination

[minajalalian90]

 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.

[rubenmarin]

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)

[rubenmarin]

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;