Multi-Field Formula - Is this a use case?

[JohnMaty]

HI All,

I have a table like below:

RptCategory	Grade	2015	2016	2017	2018
AGGRAVATED ASSAULT	Felony	160	153	188	45
AGGRAVATED ASSAULT ON TEACHER	Felony	108	103	123	31
ARSON	Felony	19	24	14	4
AUTO THEFT RELATED	Felony	95	165	109	27
AUTO THEFT RELATED	Misdemeanor	10	15	10	0

I am trying to do a % change year over year for each RptCategory.  Would I use the Multi-field tool?

I want to end up with something like this:

RptCategory	Grade	2015-2016	2016-2017	2017-2018
AGGRAVATED ASSAULT	Felony	-4%	23%	-76%
AGGRAVATED ASSAULT ON TEACHER	Felony	-5%	19%	-75%
ARSON	Felony	26%	-42%	-71%
AUTO THEFT RELATED	Felony	74%	-34%	-75%
AUTO THEFT RELATED	Misdemeanor	50%	-33%	-100%

Keep in mind that this will need to adapt year over year.

Thanks in advance!

[StephenR]

The regular formula can create the new columns using the year over year information.  If you want it to update automatically, you may want to look into the Dynamic Formula in the CREW Macro Pack.  That might allow you to test for a new year column and automatically create the new field.

[JohnMaty]

I have the CREW Macros and use them often but I have never used that one.  I am not sure how to use the normal formula to do this considering the column year will change.

[MarqueeCrew · Accepted solution]

@JohnMaty,

I've solved your post with the use of 2 multi-row formulas.  One is used to calculate the delta and the other is to calculate the year header values.

This is dynamic and will work for many years of data.

Cheers,

Mark

[BenMoss]

Unfortunately I don't see you being able to use the multi-field formula too in this instance.

The idea of the multi-field formula tool is if you have a common denominator. I.e. if you wanted to do 2018 v 2017 and 2018 v 2016 and 2018 v 2015 for instance.

In order to achieve your goal in this instance I would do a transpose.

I would then use a multi-row formula tool to get your %'s before cross-tabbing back.

My % change calculation is slightly different to yours and based on the logic in this post (https://www.calculatorsoup.com/calculators/algebra/percent-difference-calculator.php), but I'm sure you can adjust that accordingly if you need.

See the attached solution.
Ben

[BenMoss]

Ha, same as @MarqueeCrew (Y)