topic Re: Aggregate a value to the Maximum date: Rolling 12 in QlikView

Aggregate a value to the Maximum date: Rolling 12

Fri, 16 Jun 2017 01:29:32 GMT

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.

Re: Aggregate a value to the Maximum date: Rolling 12

Sergey_Shuklin — Fri, 16 Jun 2017 06:21:43 GMT

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.

Re: Aggregate a value to the Maximum date: Rolling 12

Fri, 16 Jun 2017 06:48:38 GMT

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

Re: Aggregate a value to the Maximum date: Rolling 12

Fri, 16 Jun 2017 07:26:39 GMT

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

Re: Aggregate a value to the Maximum date: Rolling 12

Fri, 16 Jun 2017 07:39:48 GMT

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

Re: Aggregate a value to the Maximum date: Rolling 12

Sergey_Shuklin — Fri, 16 Jun 2017 14:25:56 GMT

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.

Re: Aggregate a value to the Maximum date: Rolling 12

Sergey_Shuklin — Fri, 16 Jun 2017 17:15:08 GMT

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.

Re: Aggregate a value to the Maximum date: Rolling 12

Fri, 16 Jun 2017 18:21:41 GMT

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

Re: Aggregate a value to the Maximum date: Rolling 12

antoniotiman — Sun, 18 Jun 2017 05:53:43 GMT

Hi Cam,

see Attachment.

Regards,

Antonio

Regards,

Antonio

Re: Aggregate a value to the Maximum date: Rolling 12

Mon, 19 Jun 2017 20:34:49 GMT

I tried this and it didn't quite work.

I will post a QVW if management wants to pursue.