Aggregate All Values and Group by Max Date

[jcdatasax]

Hi Experts:

I'm quite stuck here.  I'm attempting to sum **ALL** values in a column and group by Date field is equal to or less than Previous Month...  please see sample data below.

dataTemp:
ID Sale Date
1 23  1-12-19
1 24  1-15-19
1 45  2-2-19
2 45  1-29-19
3 100  2-1-19
3 30  2-15-19
4 35  2-2-19
1 5  3-13-19 (current date)
2 7  3-1-19

from table

DateGroupby:
Load *,
sum(sale) as sale.sum,
If(MonthsStart(Date <= MonthStart(AddMonths(Today(), -1)), Date(MonthEnd(Date))), date(Max(Date))) AS updated_date
 Resident dataTemp
 group by Date;
 
result

ID Sale Date
1 47  1-31-19

2 45  1-31-19

1 45  2-28-19
3 130  2-28-19
4 35  2-28-19
1 5  3-13-19 maxdate(current month)
2 7  3-13-19 maxdate(current month)

[_ylc]

Your group by should be by MonthEnd. Grouping it by date will only output all the dates you have.

LOAD ID, MthEnd, SUM(Sale) as [sale.sum]

group by ID,MthEnd;

Load

*,

MonthEnd(Date) as MthEnd

resident dataTemp;

Now if you want it filtered by the maximum date, you can make a small table that takes the maximum date and put it in a variable.

LOAD max(Date) as mxDt resident dataTemp;

let vMxDt = Peek('mxDt', -1); //then use Peek function to assign it to a variable.

You can now then add a filter in your where condition:

LOAD ID, MthEnd, SUM(Sale) as [sale.sum]

where Date<='$(vMxDt)' //where condition added for above

group by ID,MthEnd;

Load

*,

MonthEnd(Date) as MthEnd

resident dataTemp;

[jcdatasax]

Thank you _ylc, I couldn't get this to work for me...  the variable provided does not work to iterate based on each row.   The goal is to do a SUM and group by MonthEnd(date) for all dates prior to the current Month.  I'm working with over 80M rows of data and  I need to reduce the data load by summarizing the data for improved UI on the front-end.