4 Replies Latest reply: Dec 18, 2015 2:51 PM by David Capan

# 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 Salesman Jul-15 Aug-15 Sales% IP% RF% OT% Sales% IP% RF% OT% Salesman1 72.73% 18.18% 4.04% 88.89% 78.45% 28.45% 0.86% 85.34% Salesman2 66.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?

• ###### Re: Comparing sum of previous record

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.

• ###### Re: Comparing sum of previous record

Hey Max,

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?

• ###### Re: Comparing sum of previous record

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

• ###### Re: Comparing sum of previous record

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).