DC
Hi ,
Having a bit of a brain freeze and wondered if anyone has the solution.
I have a spreadhseet with following columns: Date -- Invoice $ -- Salesperson, it is several hundred rows & often with numerous invoices on same date.
I would like create a speedometer for each salesperson which indicates SALESPERSONS PERFORMANCE against ACTUAL BUDGET either daily or monthly.
The formula is quite simple being total sales/number of days (or months) to date from 1/1/12.
If anyone can point me in the right track it would be most appreciated.
I can filter by salesperson and also set up the thresholds, but coming up with the correct datafeed is where my problem is.
Many thx in advance,
Dale
Hi,
If you want to do all steps into mashzone I suggest the following:
1 - Define your excel source.
2 - "Copy" it
3 - To be done twice : delete invoice column > Most recent then oldest date with salesperson dimension
4 - combine the two "copies" and you should have one line per salesperson and two date columns "most recent" and "oldest"
5 - here you calculate gap between the two dates to find the number of days - see http://www.ariscommunity.com/users/andretogo/2011-07-31-calculation-time-between-two-dates-different-lines
6 - From your copy operator (see "2"), do the following:
6.1 - delete date column
6.2 - aggregation operator with salesperson as dimension and invoice as "sum"
6.3 - result - total invoice per salesperson
7 - combine the result of 5 and of 6.3
You have the following columns: salesperson / total invoice / number of days (maybe more...) and then you can use the arithmetic operator to calculate the average invoice per day.
Hope this will do. this is surely not the only option.
BR
Nicolas
