7 Replies Latest reply on Oct 4, 2018 8:27 AM by Joe Oppelt

# Assigning a month to 12 columns to plot against time

I'm importing 1 row per id 12 columns each representing the same variable at different months using SQL and I'm having trouble trying to figure out how to assign a month to each column.. Is there any way to do this?

My data before was 12 rows per id and this allowed me to track the variable across time very easily but made it difficult to show percentages and changes on the id level. Or was my dataset the way it was before the only way to do this?

• ###### 1. Re: Assigning a month to 12 columns to plot against time

Stick with 12 rows.

You can get a total across those 12 rows doing something like this:

{ FIXED [ID] : SUM([Sales]) }

This will add up [Sales] for all 12 rows for each [ID].  You can do your percentage math using that calc.

• ###### 2. Re: Assigning a month to 12 columns to plot against time

Alright, do you think you could provide an example of calculating the percent change over a month?

• ###### 3. Re: Assigning a month to 12 columns to plot against time

Do you want to do all sales from Month-A to Month-B?  Or sales from month-A to Month-B within each ID.

Upload a sample workbook.  I'll show you how to do both.

• ###### 4. Re: Assigning a month to 12 columns to plot against time

So I'm looking for the percent change from one grouping to another. The grouping is determined by the monthly sales value and is labelled 'LOW','MED' or 'HIGH'. I'm trying to find the percentages of movement from say 'LOW' to 'MED', 'MED' to 'HIGH', 'HIGH' to 'HIGH' etc..

I was able to easily make two calculated fields on two sales columns when all of my sales data was in columns by month, but I dont know how to track the movement of the sales numbers from one group to the next anymore.

I can't seem to find a way to attach a sample book..  so here's a google drive link: SampleBook.twbx - Google Drive

• ###### 5. Re: Assigning a month to 12 columns to plot against time

I got your workbook, and I added sum(sales) to the sheet.  It looks like this:

Not all groupings are represented on each month.  Only one group exists in January, so I assume you want no number there.

February has no MED.  Do you want a percent change from High to Low there?  Or do you have to have Medium to compare against High?

You said:

...I'm trying to find the percentages of movement from say 'LOW' to 'MED', 'MED' to 'HIGH', 'HIGH' to 'HIGH' etc..

What does HIGH to HIGH mean?  Are you actually moving from month to month, rather than from grouping to grouping?

• ###### 6. Re: Assigning a month to 12 columns to plot against time

What does HIGH to HIGH mean? Are you actually moving from month to month, rather than from grouping to grouping?

HIGH to HIGH as in what percentage of the accounts that were in the HIGH grouping last month are in the HIGH grouping this month. Essentially a transition matrix like the one above showing the movement of the accounts to different different groupings on a monthly basis.

For e.g the 86% in the above "Markov Chain Transition Matrix Bar" refers to the fact that 86% of the IDs that were in grouping 1 last month are in grouping 1 this month.

February has no MED. Do you want a percent change from High to Low there? Or do you have to have Medium to compare against High?

Its not necessary, the data I'm working with will be large enough to make sure that doesn't happen.

Thanks for your help,

• ###### 7. Re: Assigning a month to 12 columns to plot against time

In the attached I added two calcs.  (Both are identical in syntax, but the table calc settings are different for each.)

[%Growth in Group] cycles through each group, and within each group it cycles through the months.  It restarts with every Group.

[%Growth in month] cycles in the opposite order and restarts every month.

I think this is what you are looking to do.

For the record, the easiest way to code up a %Difference calc is to do a quick table calc for %Difference and then just drag that pill into the calc editor to make your own version of it.  Tableau expands the underlying syntax from the quick table calc into your editor.