Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello dear community,
I have a table of data with columns like monthNumber, Sales, Team, etc..
What I need is to create a column, which will show:
For monthNumber 1 - Sum of Sales for monthNumber 1
For monthNumber 2 - Sum of Sales for monthNumber 1+2
For monthNumber 3 - Sum of Sales for monthNumber 1+2+3
and so on.
I need to keep the monthNumber column intact, as the data will later be displayed on a bar chart.
I tried to create a new column [Sales Modified], but didn't really succeed.
Thank you in advance for your help!
Try like:
table1: Load * Inline [ monthNumber,Team,Sales 1,AAA,10 1,BBB,10 1,CCC,10 2,AAA,15 2,BBB,15 2,CCC,15 3,AAA,10 3,BBB,10 3,CCC,10 ]; NoConcatenate table2: Load *, If(peek(Team)=Team, RangeSum(peek(Accumulated),Sales), Sales) as Accumulated Resident table1 order by Team,monthNumber, Sales; Drop Table table1;
Thank you for your response .
As far as I understand, it would be best for me to have it in the script, as this "accumulated" sales is then displayed on a bar chart sorted by month.
Sample attached.
Try like:
table1: Load * Inline [ monthNumber,Team,Sales 1,AAA,10 1,BBB,10 1,CCC,10 2,AAA,15 2,BBB,15 2,CCC,15 3,AAA,10 3,BBB,10 3,CCC,10 ]; NoConcatenate table2: Load *, If(peek(Team)=Team, RangeSum(peek(Accumulated),Sales), Sales) as Accumulated Resident table1 order by Team,monthNumber, Sales; Drop Table table1;
That's true only for the first record in the load sequence. And peek() works silently in such cases without throwing error and halting..