Set analysis in straight table

[aisolomatin]

Good day!

I need to calculate Sum(Sales) by quarters, excluding sales in last month each quarter.

Data:

The "to be" table should looks like this:

The table "as is" now look like this:

I can estimate MaxMonth in each row, but for Sum(Sales) expression it doesn't fit.

Expr. for maxMonth:

Max(Month_numberReport)

Expr. for Sales(no last month):

Sum({<Month_numberReport=e({<Month_numberReport={$(=Max(Month_numberReport))}>}Month_numberReport)>}Sales)

Is there any ideas?

Thank you.

[sunny_talwar · Accepted solution]

Try this

=Sum(Aggr(If(DateReport <> Max(TOTAL <QuarterReport>DateReport), Sum(Sales)), QuarterReport, DateReport))

[aisolomatin]

Thanks, Sunny! It works