topic Using AGGR in set analysis in QlikView

Using AGGR in set analysis

Tue, 23 Aug 2011 20:02:01 GMT

Hi,

I am having an issue with a statement in a chart. My goal is to return the sum of [Entry Amt] by distinct [Entry No] for the Current Fiscal Year -1. My statement below is currently returning a zero value When my current selection is 2011, June. It should be returning the summed value for 2010, June.

sum ({$<[Fiscal Year]={$(=([Fiscal Year]-1))},[Trans Mode]={'AIR COLLECT'}>}AGGR(SUM(DISTINCT [Entry Amt]), [Entry No]))

In a seperate chart, I am doing the same thing but for the currently selected fiscal year, and it is returning properly.

Any advice would be greatly appreciated, thanks!

Using AGGR in set analysis

stevedark — Tue, 23 Aug 2011 21:42:57 GMT

I suspect that you need to be ignoring more fields in the Set Analysis statement.

If you try replacing the first $ with the digit 1 (ie. ignore selections) does it give data for the the correct year? This is not a solution - just something to analyze the problem.

Define fields to ignore from the selection by listing them with just an equals after, eg. Date=,MonthYear=,.

That statement will fail if you have more than one possible Fiscal Year, suggest that you put a Max around it before taking off the minus 1.

You probably want to specify the max month as well - but make sure you get the max month in the max year - not just the max month (which could well be December the previous year).

I always find that creating variables for working out max date values and prior date values makes things much easier. I've uploaded a Shared QlikView that gives an example of how this can be done:http://community.qlik.com/qlikviews/1113 .

Finally,I would try putting the Set Analysis inside the SUM within the AGGR - not sure if it will make any difference - but it feels more correct.

Hope that helps.

Steve

Re: Using AGGR in set analysis

Wed, 24 Aug 2011 17:29:49 GMT

Steve,

I am able to successfully return the value I am looking for on current year -1 if I remove the AGGR from the statement. So for example:

sum({$<[Fiscal Year]={$(=([Fiscal Year]-1))},[Trans Mode]={'AIR COLLECT'}>}[Entry Amt])

This will return an amount for current year - 1 properly, but does not return the correct value. The AGGR is necessary for data purposes and works when it is current fiscal year, but will not when used with the set analysis of fiscal year -1.

Am I breaking a rule in my set analysis with the AGGR in the expression?

Re: Using AGGR in set analysis

Wed, 24 Aug 2011 20:20:29 GMT

try this

AGGR(sum ({$<[Fiscal Year]={$(=([Fiscal Year]-1))},[Trans Mode]={'AIR COLLECT'}>} DISTINCT [Entry Amt]), [Entry No])

Using AGGR in set analysis

stevedark — Wed, 24 Aug 2011 21:44:15 GMT

You need to place the Aggr around the Sum that works for you - rather than just around the [Entry Amt] field. I don't believe the DISTINCT statement will be neccesary.

So you will wind up with:

=sum(Aggr(sum({$<[Fiscal Year]={$(=([Fiscal Year]-1))},[Trans Mode]={'AIR COLLECT'}>}[Entry Amt]), [Entry No]))

Not dissimilar with what you started with, just in a slightly different order.

- Steve