Forum Discussion

tagban's avatar
tagban
Icon for Helper I rankHelper I
2 years ago

Building a Month over Month Total with columns one each per month

I have seperate columns, one each identifies as a Month # (1-36)
I need to display a chart that shows the change over time but can't seem to get this to work. Each row has a unique ID connected to a person, and each person has 36 columns after them with decimal based data in them.

 

IE:
Person1ID,DateLastUpdated, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 etc.

The idea is that each month we get updated data and this represents the  last 36 months of sales per person. I just can't seem to wrap my head around the logic needed to convert this to a chart.  Maybe its just too late in the day so I'll come at this again tomorrow but hopefully someone can help if nothing else point me in the right direction. I was going to build out a calendar table, Month over month, with the 1st of each month, and then use that to link the two. 

Results ideally would be in a table/chart showing each month
1 | 2 | 3 | 4 | 5 | etc
120 | 39 | 49 | 33 | 2 


Example data could be 36 months worth of sales numbers Lets just use some dummy data:

UserID,Month1,Month2,Month3,Month4,Month5
a12345,35,29,129,42,15

a93124,99,29,20,120,16

a61238,22,42,44,55,60

 

Chart would only be displayed for one employee at a time, but once again 36 total months plotted out with the left or right side showing 
User would click on employee ID and it would display chart

200                               189  

150                         149  

100                   105

50                 49

0             22

 Mths:      1    2    3    4    5

5 Replies