Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Say I have the following table where month is a dimension. Sum data is calculated with Sum(data)
Month | Sum data |
01/07/2019 | 1 |
01/08/2019 | 2 |
01/09/2019 | 3 |
01/10/2019 | 4 |
01/11/2019 | 5 |
How can I calculate the sum for the previous month, i.e.
Month | Sum data | Sum data previous month |
01/07/2019 | 1 | - |
01/08/2019 | 2 | 1 |
01/09/2019 | 3 | 2 |
01/10/2019 | 4 | 3 |
01/11/2019 | 5 | 4 |
Try in chart second expression like:
=Above( Sum(Sales))
Hi, thanks a lot for your reply.
I thought I would make the question as simple as possible but it seems to have been a mistake, I wasn't able to fully convey what I want to do.
The data I have also contains a second dimension, "saleperson". What I am trying to accomplish is calculate how much John did this month vs how he did in the previous month, same for Mike.
I can't seem to be able to use above() for this, since it return the other sales person record. Any ideas how what I am trying to do can be done?
Month | Salesperson | Sum data | % increase |
01/07/2019 | John | 10 | - |
01/07/2019 | Mike | 5 | - |
01/08/2019 | John | 100 | 900% (from 10 to 100) |
01/08/2019 | Mike | 20 | 300% (from 5 to 20) |
To achieve similar output in chart - it would be easier if you could change the chart construct a bit. Put Salesperson as dimension (rows) and bring the months in column, then you could use before() like above() to get the variance. Otherwise, you could take a script-route to calculate it.
I am not sure I understand how I can put the months in columns without it aggregating aggregating all the sales numbers for a single Saleperson over the entire months.
However, I simply want to get the number I've explained above (month of month % difference of sales for a single saleperson). I may want to plot this in a graph, and not in a table. How would you approach this using scripting?
Thanks
If you have more than two months data, how would you like the output to be?
The value I am trying to calculate is:
John's value for month M = % difference (Sum of sales for John in Month M, Sum of sales for John in Month M - 1)
Mikes's value for month M = % difference (Sum of sales for Mike in Month M, Sum of sales for Mike in Month M - 1)
So the output could be, for instance, a bar chart, with the dimension being the month, and for each month M two bars are shown, one for Mikes's value for month M and one for John's value for month M.
Or we could imagine a line chart, where dimensions (x axis), is again month, or time, and each line represents the salesperson's % difference of the month vs the previous month.
Why two bars always, number or salesperson also could be many right?
Yes correct. I said two in the context of the example with our two salesperson, but there will be more than two in reality.