Filling in a column based on the vale of multiple rows and columns.

[edmonj]

Looking for a way I can fill in a column at the end of my crosstab with target values for the most recent week in the rows that correspond with that target.

I have a data set that originally looks like this:

Week	Thing1	Thing2	Thing3	Thing4	Target1	Target2	Target3	Target4
1	62	148	10	147	351	163	319	32
2	366	37	31	86	360	183	257	296
3	238	70	124	122	333	190	313	189
4	394	141	95	65	194	335	124	51
5	352	72	172	88	60	85	345	56

That I transpose and crosstab to get to this:

Key	Name	1	2	3	4	5	WoW	Target
1	Thing1	62	366	238	394	352	-10.7%
2	Thing2	148	37	70	141	72	-48.9%
3	Thing3	10	31	124	95	172	81.1%
4	Thing4	147	86	122	65	88	35.4%
5	Target1	351	360	333	194	60	-69.1%
6	Target2	163	183	190	335	85	-74.6%
7	Target3	319	257	313	124	345	178.2%
8	Target4	32	296	189	51	56	9.8%

I'm looking for a way to have the target column show the latest week in the case 5 target for each Thing.

So the end result would look like this (I'll have to add a % to target as well but if I can get how to bring the target over this isn't an issue):

Key	Name	1	2	3	4	5	WoW	Target	% to Target
1	Thing1	62	366	238	394	352	-10.7%	60	586.7%
2	Thing2	148	37	70	141	72	-48.9%	85	84.7%
3	Thing3	10	31	124	95	172	81.1%	345	49.9%
4	Thing4	147	86	122	65	88	35.4%	56	157.1%

I've tried a few things but have yet to get anything that works.  I'm open to either adding it in before transposing and cross tab, but I add the keys after the cross tab to order my report render accordingly and thought it would be an easy thing to reference as out of my 59 rows/keys only 12 have targets.

[DavidSkaife · Accepted solution]

Hi @edmonj 

Here is one way you could do it:

Transpose the data into a vertical alignment, then filter on where Name contains "Thing", Cross Tab as normal on the True output, but on the False output (which contains the "Target" section) sort by Week and Name (to ensure its in the right order for the next stage). Using the Summerise tool group by Name and find the last value, replace "target" with "Thing", join back together and sort on Name to get the final output.

You can then add in the other fields/calcs you may need.

This should also be dynamic so if you had Target/Thing6, 7, 8 etc it will handle it without any tweaking.

[binuacs]

@edmonj what is the logic to calculate WOW% and Targt %

[edmonj]

This works up to the point of the join back.  Where I have other 'things' that don't have targets the join is excluding them.

[edmonj]

WoW is just ([5] - [4]) / [4].

% to target will just be last week vs the target so in this case [5] / target

[BS_THE_ANALYST]

@edmonj got carried away with this one. Was enjoying trying to figure out your calculations:

It's pretty dynamic. I know you likely will be changing the names of 'Things'. If you keep the names of the columns with TargetX this should always work I think. The only thing you need to change in the workflow as more weeks get added:

[DavidSkaife]

Hi @edmonj 

In that case you should be able to add a Union tool after the join, and bring the Left output back into the workstream

[edmonj]

@DavidSkaife that works perfectly.  thank you.

[BS_THE_ANALYST]

@edmonj okay, it should be completely dynamic now. The WoW, Target, and % to target will be automatically calculated as you add more weeks to the input.

[edmonj]

@BS_THE_ANALYST

Thank you for this as well.  One of the other things I needed to figure out eventually was getting the WoW and everything to update without me manually changing the weeks before my final output so this will be useful.