Ask Your Question
0

Calculate difference between columns in report (pivot table?) [closed]

asked 2014-05-11 17:21:45 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Hi,

I can not get my head around seemingly simple problem. Suppose I have the following data in Sheet (this is much simplified example, see below). What I want to do is create a report that would have difference compared to previous month per each person/Name, I do not need no sum totals, just difference compared to previous month. In future I would need to create a monthly report each month. Ideally I would also want to filter by Group at some point. I tried Pivot tables, but there just seems no way I can get it done with my current knowledge.

Maybe it is not job por Pivot tables at all? Maybe I can get this same thing done different way, the only requirement being I do not mess with data in original Sheet.

Data:

Name    Group   March   April   May
John    1       284     286     287
Mark    2       299     302     303
Dough   1       236     243     249
Stella  3       308     310     313
Fred    2       232     235     238
Vicky   3       316     318     320
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-22 15:24:44.840705

2 Answers

Sort by » oldest newest most voted
1

answered 2014-05-12 00:18:56 +0200

m.a.riosv gravatar image

@David answer it's fine if you want maintain the data as you have.

The way to do it with Pivot Table is having the data tabulated, one value one record, how you have the data seems a report from row data.

Next a sample file on how organize data to do it with pivot table: SamplePivotTableWithDifferences.ods
so it's possible filter the fields by month or name or set up a general filter.

Editing the pivot table and double click on data field to set up for differences.

image description

edit flag offensive delete link more

Comments

That's some serious pivot-tabling! B-) I assume you have the pivot table on the same sheet as the data simply to illustrate? Otherwise it's hard to see how data is added on this scenario. But if that assumption is correct, this looks extremely versatile. Thanks!

David gravatar imageDavid ( 2014-05-12 00:32:48 +0200 )edit

Your assumption is correct, because it's not possible insert/delete rows/columns in the area of the pivot table, but sometimes like this is useful.

m.a.riosv gravatar imagem.a.riosv ( 2014-05-12 01:40:48 +0200 )edit

I had also had this idea of transposing my data as marisov, but it seemed unnatural to my mind. Anyway, thanks for this example! I will have to reconsider my data arrangement.

GWolf gravatar imageGWolf ( 2014-05-12 09:17:02 +0200 )edit
1

answered 2014-05-11 18:43:22 +0200

David gravatar image

I'm very much an amateur when it comes to Calc, but you can try the sample file, attached (populated with your data), for one solution:

  1. (Based on code found at Ask.metafilter.com.) Find the last number in the row, and subtract from it the next-to-last number in the row - using (in pseudo-code): INDEX(FirstCell:LastCell,COUNTA(FirstCell:LastCell)) to reference the cells. I put these in a new column C, between group and months (which I expect are going to extend). I subtracted, because it looks like your "norm" is get bigger values month on month.
  2. Reference those values in a second sheet to make your report easier (could be skipped!).
  3. Set up pivot table between "Group" and "Last Month Diff" columns. Each month, update the values in the pivot table by right-clicking on the table, and selecting "Refresh" from the pop-up menu.

Hope that helps. There is probably a better way to do this, but my sample file (below) is at least working ... and I learned something, anyway. :)

Attached sample file: Diff_to_previous_month_example.ods

edit flag offensive delete link more

Comments

David, thanks, thats at least a start. But I am not entirely sure why there is a SUM() in formula calculating the difference. To me it seems, that instead of =SUM((INDEX(D2:AAB2;COUNTA(D2:AAB2))-(INDEX(D2:AAB2;COUNTA(D2:AAB2)-1)))) it should suffice to specify =INDEX(D2:AAB2;COUNTA(D2:AAB2)-(INDEX(D2:AAB2;COUNTA(D2:AAB2)-1))).

GWolf gravatar imageGWolf ( 2014-05-11 19:03:02 +0200 )edit

Yep, =(INDEX(D2:AAB2,COUNTA(D2:AAB2))-(INDEX(D2:AAB2,COUNTA(D2:AAB2)-1))) will do it. I did warn you I'm an amateur! ;) Otherwise, it seems to work fairly well.

David gravatar imageDavid ( 2014-05-11 21:08:05 +0200 )edit

Question Tools

2 followers

Stats

Asked: 2014-05-11 17:21:45 +0200

Seen: 20,435 times

Last updated: May 12 '14