Comparing sum of previous record

[Anonymous]

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-15								Aug-15
Salesman		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-15								Aug-15
Salesman		Sales flag		IP%		RF%		OT%		Sales%		IP%		RF%		OT%
Salesman1		0		1		0		1		1		1		0		0
Salesman2		1		1		0		0		1		0		0		1

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?

[maxgro]

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]

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?

[#4 · Accepted solution]

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]

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!