How to calculate numbers from one file to another without the same reference

Question

Hi all,

I'm again struggling with something which seems to look easy to solve, but I can't fix the puzzle myself. Hope someone here can help me out!

I have 2 different input files (see attached file as an example)

The first one shows the share of the total per agegroup per track.

The second one shows the share of the total per agegroup per playlist.

Now I want to calculate the variance per agegroup between each track and the various playlist.

So, if for track A, 20% of the audience is from the age group 10-30, and for playlists 1 it's 10%, then I want to see a 200% (20/10) as a score for that track for that specific playlist and so on.

How can I do this.. I can't join the files first, right and union feels weird..

Find attached an excel file which hopefully clarifies the issue a bit better.

thanks again!

Best,

Floris

example_Alteryx.xlsx

bsharbo · Accepted Answer

I have attached a workflow that should solve your problem. You will have to re-order the columns to the order you want on the output, but the idea is sound in theory (you can dynamically re-order columns if needed).

Note: the key is to pivot your data into a typical table format so that you can join on the column values (10-30) as that is effectively what you are asking to join on.

Let me know if you have any questions.