topic Re: Comparing sum of previous record in QlikView

Comparing sum of previous record

Anonymous — Thu, 17 Dec 2015 20:43:40 GMT

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

Salesman2

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

maxgro — Thu, 17 Dec 2015 20:53:09 GMT

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

Anonymous — Thu, 17 Dec 2015 21:11:47 GMT

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?

Re: Comparing sum of previous record

Fri, 18 Dec 2015 10:48:00 GMT

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

Anonymous — Fri, 18 Dec 2015 19:51:57 GMT

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!