Aggregate a value to the Maximum date: Rolling 12

[#1]

We have rolling 12 data where at the end of every month an automatic adjustment happens to offset rounding and conversion errors.

Each TRANSACTION_TYPE for adjustments get an "A" .    I would like to accumulate the sum of A and have it appear at the END date for the rolling 12 (MAX(DATE(enddate))

Take this data and transform

MonthYear	Type	Total Cost	Balance
Jun-2016	BEG	$    0.00	$0.00
Jun-2016	R	$    10,390.75	$10,390.75
Jun-2016	I	($    10,390.72)	$0.03
Jun-2016	A	($    0.03)	$0.00
Nov-2016	R	$    5,093.93	$5,093.93
Feb-2017	I	($    5,093.92)	$0.01
Feb-2017	A	($    0.01)	$0.00
Jun-2017	END	$    0.00

As an example with this data, I would like everything to be the same except to accumulate the A's and add them to the last month of the range so it would look like this.

MonthYear	Type	Total Cost	Balance
Jun-2016	BEG	$    0.00	$0.00
Jun-2016	R	$    10,390.75	$10,390.75
Jun-2016	I	($    10,390.72)	$0.03
Nov-2016	R	$    5,093.93	$5,093.93
Feb-2017	I	($    5,093.92)	$0.01
Jun-2017	A	($    0.04)	$0.00
Jun-2017	END	$    0.00

My only thought is to make a table with a composite, sum the A's and give it a MAX(DATE) fucntion KEY and rejoin it to the main table witha different transaction type and then exclude the As.

[Sergey_Shuklin]

Hello, Cam!

How is an A became a Jun-2017?

Nevermind i've got it.

If you want it in back-end so yes, you'll have to create a union aggregated table with sum of As and max date, and remove As from original table.

Or you can add a total row of As (without removing them from original table) and make a mark column if(Type='A',0,1), after that you can filter your data with expressions.

[#3]

I want to force it to the same date as the ending balance

[#4]

I would take an easier way in the front end if there is an answer

[#5]

I also thought of just making another TranType when rejoining. called ADJ

[Sergey_Shuklin]

Found for you the best solution!

Create a straight table (or pivot doesn't matter), add calculated dimension:

=if(Type='A',date($(#=max(MonthYear))),MonthYear) //this will create a dimension which picks a max date for Type A.

Add second dimension Type to see that all is correct.

After that you can create an expression sum([Total Cost]) and thats all. Works fine.

If don't I'll make an example on your data.

[Sergey_Shuklin]

Catch the example!

I use makedate() function and textbetween() because dates and values from site was gathered like a string.

I suppose you won't have to do this with your data, but maybe some function will take you attention.

[#8]

Thanks. I will try this over the weekend and report Monday and load my data if there is a difference.

[antoniotiman]

Hi Cam,

see Attachment.

Regards,

Antonio

Regards,

Antonio

[#10]

I tried this and it didn't quite work.

I will post a QVW if management wants to pursue.