topic Re: Aggr Function - Eliminating Values Not Selected in QlikView

Aggr Function - Eliminating Values Not Selected

joshabbott — Wed, 10 Aug 2011 19:21:29 GMT

Hello,

I'm having an Aggr Function Issue. Here is my situation:

I have a table of SalesPeople and SalesAmounts Stored with Years:

In my QlikView application, I have selected say 2010.

What I want to return is the SalesAmount (plus other calculations at the SalesPerson level) for Sales in 2011.

Say I have this data:

SalesPerson SalesYear SalesAmount

John Smith 2010 $50

John Smith 2011 $100

Jane Jones 2011 $200

I have this expression in a chart:

sum

(

aggr

(

alt

(

sum

(

{$<SalesYear={2011}>} SalesAmount

),0

),SalesPerson

)

// There are divides and other calculations that force me to do calculations @ the Salesperson level.

)

I would expect to get $300 because of the two records from 2011 ($100 & $200). I notice, however, the aggr function eliminates the Jane Jones record of $200 because this SalesPerson doesn't exist in the original selection of 2010, so I only get $100 back.

Does anyone have any suggestions for me? Thank you for your help!

Aggr Function - Eliminating Values Not Selected

ToniKautto — Wed, 10 Aug 2011 20:21:31 GMT

The set expression you have set up will eliminate your 2010 selection and replace it with 2011, so it will give you sales sum $100 for John Smith and $200 for Jane Jones. If this is not what you get then there must be something else causing this, so please provide a sample QVW to enable more detailed evaluation.

Re: Aggr Function - Eliminating Values Not Selected

joshabbott — Thu, 11 Aug 2011 00:19:44 GMT

Thank you for the reply. I'm attaching a sample that shows how this doesn't work. Column 1 is the column I believe should work because I'm doing the aggr, but the record for Jane Jones is showing no value, because 2010 is selected and there is no Jane Jones in 2010. Since a sum wouldn't make sense for my aggr problem, I also added a new divide field to show that I do need to do an aggr function I believe to accomplish what I want. Column 2 doesn't use the aggr and you can see it is off. My total number should be 9, 5 for John Smith, 4 for Jane Jones. Thank you for any help you can give me!

Re: Aggr Function - Eliminating Values Not Selected

joshabbott — Thu, 11 Aug 2011 12:03:38 GMT

One other comment on the test file I sent, to see the correct answer, if you select '2011' for the 'SalesYear' you will see what I want to get in column 1 (4 & 5 - Total 9). Unfortunately, 2010 will be selected and I can't change this selection to 2011. Thank you!

Re: Aggr Function - Eliminating Values Not Selected

ToniKautto — Thu, 11 Aug 2011 18:47:19 GMT

You do not need to aggregate the Sum() over the SalesPerson, as the function will already be aggregated over SalsPerson as this is the dimension. See the attached sample for a working expression.

Re: Aggr Function - Eliminating Values Not Selected

joshabbott — Thu, 11 Aug 2011 19:06:39 GMT

Thank you for the example, but in this situation, I do need to use the aggr. I am returning a bar chart with the values in it. I just tried to simplify what I'm doing as much as possible in my example. I'm attaching another example that shows the bar that I want to have display 9, instead, it is displaying 5 because Jane doesn't exist in 2010. It seems since the selection of 2010 is made, and Jane isn't a valid person in 2010, the aggr function is ignoring Jane even though I need my set analysis to pick her up because she is in 2011. Thank you again for your time!

Re: Aggr Function - Eliminating Values Not Selected

ToniKautto — Thu, 11 Aug 2011 19:42:43 GMT

I don't think you need aggr() as far as based on you sample. By using the TOTAL qualifier in the expression you will get the expression total. This will be in the same way as the total in the straight table. Please find new sample attached where you have the total in charts as well as in a text object.

My appologies for being a bit unobservant. You actually can shorten the expression to one sum operation, which will also be more performance friendly.

=sum( TOTAL {$<SalesYear={'2011'}>} SalesAmount / DivideBy)

Re: Aggr Function - Eliminating Values Not Selected

joshabbott — Thu, 11 Aug 2011 19:59:14 GMT

Thank you, I will try to incorporate this and see if this works for my situation. I really appreciate your help!

Re: Aggr Function - Eliminating Values Not Selected

joshabbott — Fri, 12 Aug 2011 11:24:29 GMT

Ok, I've built this into my application and we are soooooo close, I can just feel it. Part of my expression is using different search criteria. Looking at my data table from my test:

John Smith - 2010 - 50

John Smith - 2011 - 100

Jane Jones - 2011 - 200

John Smith - 2012 - 50

Jane Jones - 2012 - 50

I need to add the complexity where:

- 2010 is still the selected year

- if the Sum SalesAmount of 2011 Sales is 100, divide it by the 2012 'Divide By column' (For John Smith who has the 100 SalesAmount in 2011, the Divide by is 2 in 2012) = 100 / 2 = 50

- if the Sum SalesAmount of 2011 is not 100, just return the sum of the Sales Amount for 2011. Jane Jones has 200 in Sales Amount for 2011 = 200

I need to get the total of these (200 + 50) in my chart = 250. Right now, on the straight table we have made progress, Jane Jones is showing 200 and John Smith is showing 50, but I need these to be totaled and I tried using the 'Total' in the sum and it didn't work correctly. I have attached my sample where I tried this (The last 2 columns of the straight table).

Thank you so much!

Re: Aggr Function - Eliminating Values Not Selected

ToniKautto — Fri, 12 Aug 2011 11:44:43 GMT

I think in this case you are looking for the total sum of the calculation made per person. To resolve this you can aggregate your calulation per person, and then sum this as a total. Not sure if that makes sense to you, but in the attached file you can see that it returns the expected value at least.

As you found in your initial issue, Jane Jones is excluded as she has no values for 2010. To get around this the Sum(TOTAL) is made over the entire data set, so that Jane Jones is not excluded in the calculation.

Re: Aggr Function - Eliminating Values Not Selected

joshabbott — Fri, 12 Aug 2011 11:45:39 GMT

One more thing, I tried changing the total mode to 'Sum' but since the bar does not have dimensions, it will not give me a total on the bar.

Re: Aggr Function - Eliminating Values Not Selected

joshabbott — Fri, 12 Aug 2011 11:50:44 GMT

The only problem is when 2010 is now selected, since Jane isn't in 2010, the number on that last column goes from 250, which was correct, down to 50, which is only John Smith's number.

Re: Aggr Function - Eliminating Values Not Selected

ToniKautto — Fri, 12 Aug 2011 12:37:16 GMT

I know the original file I uploaded had that problem, so I replaced the file. Please check that you look at my latest attached file; QlikTest (4) (2) (2).qvw

Re: Aggr Function - Eliminating Values Not Selected

joshabbott — Fri, 12 Aug 2011 12:39:20 GMT

BINGO! Thank you a million, that worked like a charm!