Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.
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
BINGO! Thank you a million, that worked like a charm!