Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Within a table (not a Pivot table) I want a running daily sum conditional upon a Year column.
If the year for that row is "2019", then I want the sum to be all of the daily values so far where the year is also "2019". If the year for that row is "2018", then I want the sum to be all of the daily values so far where the year is also "2018".
In the picture below, you will see I have the "Nth day of the year" in column 1, Fiscal Year in column 2, and Sales $ in column 3. My current formula is:
RANGESUM(
ABOVE(
TOTAL SUM(NetSales)
,0
,ROWNO(TOTAL)
)
)
but unfortunately, that gives me a running sum regardless of the year. I want the sum to be conditional on the year column. You can see my desired result on the right in the column below.
Try this
Aggr( RangeSum(Above(Sum(NetSales), 0, RowNo())) , [Fiscal Year], ([Nth Day of Fiscal Year], (NUMERIC)))
Try this
Aggr( RangeSum(Above(Sum(NetSales), 0, RowNo())) , [Fiscal Year], ([Nth Day of Fiscal Year], (NUMERIC)))
That was it, Sunny! Thanks for the quick and accurate response.
I had tried using AGGR() before, but it didn't work. The key was using (... (NUMERIC)) on [Nth Day of Fiscal Year]. I haven't seen that function before.