topic Re: Conditional aggr expression in QlikView

Conditional aggr expression

Tue, 15 Nov 2011 22:11:56 GMT

Hello guys,

I am trying to do a conditional aggr function but failing.

I am trying to filter on salesman_id to find out what days he has worked.

I then wan to calculate the total sales by all salesman for that day.

e.g. in the attached spreadsheet, salesman Id 2005 has worked days 1,4,7,9 etc

I remove the filter from salesman ID and put the filter on day 1.

I then get the total of List Price for day one which is 90483.

I am struggling to put this in my expression, please help

thanks

Re: Conditional aggr expression

johnw — Tue, 15 Nov 2011 22:59:44 GMT

If I understood the problem, I think this?

sum({<Day=P(),"Salesman ID"=>} Sales)

Conditional aggr expression

Tue, 15 Nov 2011 23:54:05 GMT

Hi John,

many thanks for the response.

Always to the rescue.

The expression seems correct, howeve I will not be able to test it untill tomorrow.

I will get some feedback tomorrow.

Thanks again

Conditional aggr expression

Wed, 16 Nov 2011 00:34:30 GMT

Actually I can forsee a problem when displaying the data using salaman ID as a dimension.

It will only sum the values for Salesman ID 2005. But I want it to add the totals for the days where Salesman ID 2005 has worked.

So if Slaesman ID 2005 has worked as shown in days below and total is the total revenue for that day for sales made by all sales people.

Day	Total
1	90483
4	158462
7	38889
9	142568
11	97869
20	84376
21	166461

totals above add upto 779108

I want it to show as below

Slaesman ID	Value
2005	779108

779108

and list will go on for other salesman...

I hope thats clear.

Thanks

Re: Conditional aggr expression

johnw — Wed, 16 Nov 2011 00:49:20 GMT

Those aren't the numbers I get loading in your file. If I select just those days, use Day as a dimension, and calculate sum(Sales), I get 650623. That's the same number my expression gives if you select Salesman ID 2005.

In the expression, Salesman= means to ignore the salesman. However, Day=P() says to only include days that salesman worked. That's my understanding of what you want. The total revenue for all salesmen for the dates that salesman 2005 worked.

See attached.

However, when you say "and the list will go on for other salesmen", do you mean that you want a chart that can have salesman as the dimension and display multiple salesmen at once? That's significantly more complicated than displaying data for one selected salesman. I think it's doable from past experience, but it could be ugly.

Re: Conditional aggr expression

Wed, 16 Nov 2011 01:06:44 GMT

Unfortunately I cant open the qv file as i'm using thr QV demo version.

I am attachimg my qv file.

In the file, you can see that Salesman ID 2005 is selected. And on the right, the days he has worked are displayed.

All I want to do is show each salesman with the total of days they have worked.

You might be getting wrong results because you might have the sum on Sales where I have it on List Price

Re: Conditional aggr expression

johnw — Wed, 16 Nov 2011 16:26:57 GMT

So yes, if you change "Sales" to "List Price" in the expression I gave you, it returns 779108 for Salesman 2005.

Re: Conditional aggr expression

Wed, 16 Nov 2011 16:51:22 GMT

Sorry if I'm confusing you John, but I don't want Day as dimension.

I want Salesman ID as dimension.

Re: Conditional aggr expression

swuehl — Wed, 16 Nov 2011 17:07:01 GMT

try building your table with dimension Salesman ID and expression:

=sum( aggr(if(sum([List Price]), sum(total<Day> [List Price])), [Salesman ID],Day))

Hope this helps,

Stefan

edit: attached app

Re: Conditional aggr expression

Wed, 16 Nov 2011 17:32:20 GMT

Exactly what I'm talking about.

Thanks alot John and Stefan

Conditional aggr expression

swuehl — Wed, 16 Nov 2011 18:25:29 GMT

You're welcome.

Please note that my expression will only take days into account, where sum([List Price]) per Salesman ID is larger than zero. If your Salesman do work without resulting in revenue / [List Price] value, this will not work.

Conditional aggr expression

Thu, 17 Nov 2011 15:14:29 GMT

Thanks for the tip Stefan.

I should be OK as I don't have null or - values.

thanks