Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
joshabbott
Creator III
Creator III

Aggr Function - Eliminating Values Not Selected

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!

1 Solution

Accepted Solutions
ToniKautto
Employee
Employee

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.

View solution in original post

13 Replies
ToniKautto
Employee
Employee

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.

joshabbott
Creator III
Creator III
Author

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!

joshabbott
Creator III
Creator III
Author

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!

ToniKautto
Employee
Employee

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.

joshabbott
Creator III
Creator III
Author

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!

ToniKautto
Employee
Employee

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)

joshabbott
Creator III
Creator III
Author

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

joshabbott
Creator III
Creator III
Author

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!


ToniKautto
Employee
Employee

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.