topic Re: Sum for previous month with month as dimension in App Development

Sum for previous month with month as dimension

johnnyjohn — Sat, 16 Nov 2024 02:55:58 GMT

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

Re: Sum for previous month with month as dimension

tresB — Fri, 20 Mar 2020 09:48:10 GMT

Try in chart second expression like:

=Above( Sum(Sales))

Re: Sum for previous month with month as dimension

johnnyjohn — Fri, 20 Mar 2020 10:25:19 GMT

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)

Re: Sum for previous month with month as dimension

tresB — Fri, 20 Mar 2020 10:32:32 GMT

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.

Re: Sum for previous month with month as dimension

johnnyjohn — Fri, 20 Mar 2020 10:41:38 GMT

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

Re: Sum for previous month with month as dimension

tresB — Fri, 20 Mar 2020 10:59:47 GMT

If you have more than two months data, how would you like the output to be?

Re: Sum for previous month with month as dimension

johnnyjohn — Fri, 20 Mar 2020 11:31:43 GMT

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.

Re: Sum for previous month with month as dimension

tresB — Fri, 20 Mar 2020 11:17:25 GMT

Why two bars always, number or salesperson also could be many right?

Re: Sum for previous month with month as dimension

johnnyjohn — Fri, 20 Mar 2020 11:22:31 GMT

Yes correct. I said two in the context of the example with our two salesperson, but there will be more than two in reality.

Re: Sum for previous month with month as dimension

ruparaja — Fri, 20 Mar 2020 11:47:43 GMT

Hi , Have tried in Backend please find the below script

date_values:
Load
*,
peek("Sum data") as previous_month_record;

LOAD
"Month",
"Sum data"
FROM [lib://data/date_values.xlsx]
(ooxml, embedded labels, table is Sheet1);
Out put:

Re: Sum for previous month with month as dimension

johnnyjohn — Fri, 20 Mar 2020 11:58:10 GMT

Hello, thanks for answering. I have tried it, but I am not quite sure how this expands on using Above(). The issue I am having is deeper than my original question. Please see the conversations abovee Thanks!