Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Comparing sum of previous record

Hello,

I'm trying to compare the sum of a value to that same sum of a value, but for the previous record (Month in this case).

I can't quite seem to get it to work properly.

Month Jul-15Aug-15
Salesman Sales% IP%   RF% OT%Sales% IP%   RF% OT%
Salesman172.73%18.18%4.04%88.89%78.45%28.45%0.86%85.34%
Salesman266.67%33.33%0.00%66.67%75.00%25.00%0.00%100.00%

What I'm trying to get is a column, or a separate table, that will contain a flag displaying 1 or 0, if the salesmans' percentages went up, for each expression column.

Month Jul-15Aug-15
Salesman Sales flag IP%   RF% OT%
Sales%
IP%
  RF% OT%
Salesman101011100
Salesman211001001

The earliest month shown, July in this case, would compare June data, or the previous month's data, with that month.

I've tried to use set analysis with aggregate functions with no luck so far.

Any ideas?

1 Solution

Accepted Solutions
Not applicable
Author

Hi David,

This is a qvw which could help if you haven't solved your problem yet. In it you have 2 ways to do what you are looking for : using script and using before function.

Regards

Gilles

View solution in original post

4 Replies
maxgro
MVP
MVP

is that a pivot table?

maybe with before

before([ total ] expression [ , offset [,n ]])

Returns the value of expression evaluated with a pivot table's dimension values as they appear in the column before the current column within a row segment in the pivot table.

Anonymous
Not applicable
Author

Hey Max,

Thanks for the quick reply.

This is actually very helpful. I'm playing around with this function now and trying to get it work with variables in a separate chart.

Is there anyway to make it look at a previous month that isn't selected?  For instance, if I select July and August, can I make is so that the July columns will compare to the June numbers?

Not applicable
Author

Hi David,

This is a qvw which could help if you haven't solved your problem yet. In it you have 2 ways to do what you are looking for : using script and using before function.

Regards

Gilles

Anonymous
Not applicable
Author

Thanks Gilles!

I had to play with the expressions a bit since I would be getting both a 1 and a 0 for each flag if the amounts were greater, but this was solved by making the expression in the chart if(sum(Sales_flag)=1,1,0).

Thanks again for your help!