Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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.