That's going to be a super slow report, even restricted to displaying 3 months per item.  (The data can't be pre-filtered at the report selection stage).

Try using IM_ItemCustomerHistoryByPeriod data (by period, so it should be faster than raw invoice history)... grouped by item, with formulas for FirstMonthQuantity (in relation to the minimum date per item), SecondMonthQuantity, and ThirdMonthQuantity...  to sum for your three totals.

I usually do an "IsFirstMonthForThisItem" for the logic test (dragged into the report to verify the formula works as you want), then do the quantity formula using that True/False value (if IsFirstMonthForThisItem then quantityfield else 0).

That's going to be a super slow report, even restricted to displaying 3 months per item.  (The data can't be pre-filtered at the report selection stage).

Try using IM_ItemCustomerHistoryByPeriod data (by period, so it should be faster than raw invoice history)... grouped by item, with formulas for FirstMonthQuantity (in relation to the minimum date per item), SecondMonthQuantity, and ThirdMonthQuantity...  to sum for your three totals.

I usually do an "IsFirstMonthForThisItem" for the logic test (dragged into the report to verify the formula works as you want), then do the quantity formula using that True/False value (if IsFirstMonthForThisItem then quantityfield else 0).

Hi Kevin,

Thanks for the reply. Some items first sold were 3-4 years ago too and have different first invoice date, to get the first 3 months of quantity is going to be a challenge for each of those items.

Exactly.  You basically have to load data for all of item sales history from the beginning of time, and process it all in the report.  Group by item, and do a minimum on the date field (or if you use the summary table, do a formula for YYYY-MM and use a minimum of that field).  Be careful when calculating month 2 & 3, to properly handle year changes.

That's what i had to do. Thank you again Kevin.