Aggr() function

[#1]

Hi

Can anyone explain me how the Aggr() function works with beautiful example with data in different scenarios.

B'coz i'm getting confuse on this functionality. I've gone through the reference manual also..

[johnw]

DaveLyon wrote:

...My problem now is that the formula I asked about is an expression in a chart and the Month2 date is a dimension in the chart...

I'd expect it to work with neither, either or both Month fields in the chart.  I'd expect something like this to work just fine:

Dimension 1 = Customer

Dimension 2 = Month2

Expression  = sum(FlaggedValue)

I could be wrong, of course, but I'm not seeing why it would fail.

[#3]

But Month2 is a data island date used on the x-axis.  I'm trying to chart SUM( Value from BOT to Month2)  where Month2 = Jun-12 (e.g.), SUM( Value from BOT to Month2)  where Month2 = Jul-12 (e.g.), etc. (BOT = Beginning of Time).

If I understand your proposal correctly, I would need to create a FlaggedValue field for each possible x-axis month, then figure out how to sum the correct FlaggedValue field and apply it to the correct month in the chart.

[johnw]

DaveLyon wrote:

But Month2 is a data island date used on the x-axis.  I'm trying to chart SUM( Value from BOT to Month2)...

Ah, OK.  You need a completely different solution, then.  The FlaggedValue solution assumed that Month2 was a real field and on the same table as Month1.  I know of no way to fix your performance with Month2 on a data island.  So fixing your performance probably requires that you take Month2 off of the data island and link it directly to your data in a way that satisfies your requirement.

For a full accumulation of Month1s up through Month2, remove the data island and instead build a date linkage table.  For simplicity, let's say it's April 2012, and you only have data from January.  You'd want a linkage table that looks like this:

Month2,   Month1
Jan 2012, Jan 2012
Feb 2012, Jan 2012
Feb 2012, Feb 2012
Mar 2012, Jan 2012
Mar 2012, Feb 2012
Mar 2012, Mar 2012
Apr 2012, Jan 2012
Apr 2012, Feb 2012
Apr 2012, Mar 2012
Apr 2012, Apr 2012

There are a number of ways to easily build a table like that.  Now use Month2 as the dimension, and sum(Value) as the expression.  Can you see what would happen?  For the row with a Month2 of Mar 2012, QlikView will use the table above to see that you actually want the Month1s of Jan 2012 through Mar 2012 on that row, so it will add up all three months for you.  I haven't used this much in real applications, but I have used it, and it performs quite well.  The main drawback in my opinion is that you have a second Month field, which can confuse the users, but you already accepted that problem when you created your Month2 field on the data island so at least it's nothing new.

I've posted lots of examples of this sort of table and ways to build it before.  Search for "AsOf".  I usually call these AsOf tables, because I think of them as "As of Month2, what is the full accumulation of Month1?"

Edit:  Mind you, a much simpler solution is to just use full accumulation in the chart.  But I don't think I've ever done that in a live application.  If you select a month in a chart with full accumulation, it destroys the accumulation.  With the AsOf approach, the value stays what it should be.

[#5]

This is great!  I'll try your "AsOf" table approach.

BTW, the full expression contains 2 MonthDates (something like a range), so I can't use the full accumulation capabilities.  I think what I can do is to use your table with an expression like SUM(Month2) - SUM(Month1).

Also, my compliments on your clear and concise writing style.  I know it's not easy, especially on technical matters.  Thanks for that, too.

[#6]

Hi John,

Thanks for the detailed response. I'll be sure to use your recommendation !

Thanks and good luck.

Gur

[shantanu73]

Hello John Witherspoon,

I had understood the aggr functionally with the good example you had provide.

Just want to clarrify whether it can be only use in Expression?

If I want to create one more table on the base of source table the result format as follows:

Customer Max_Value

A               30

B               15

C               40

The following output can be generate from the following script also as follows:

Load Customer, max(Value) as asm resident sal_tab group by Customer;

But I want to use Aggr() is it possible to use in script. Is it possible

Shantanu

[shantanu73]

Hello John Witherspoon,

I had understood the aggr functionally with the good example you had provide.

Just want to clarrify whether it can be only use in Expression?

If I want to create one more table on the base of source table the result format as follows:

Customer Max_Value

A               30

B               15

C               40

The following output can be generate from the following script also as follows:

Load Customer, max(Value) as asm resident sal_tab group by Customer;

But I want to use Aggr() is it possible to use in script. Is it possible

Shantanu

[jolivares]

Hi John,

After I check many of your examples of AsOf, I have different situation.  Let me tell you to see if you can help me.

Date Format = DD-MM-YYYY

AsOfTab:

LOAD * INLINE [

    Cust, Balance, BalDate

    abc, 100, 31-01-2012

    abc, 200, 29-02-2012

    abc, 150, 31-03-2012

    abc, 300, 30-04-2012

    abc, 400, 31-05-2012

];

Sales:

LOAD * INLINE [

    Cust, DateSales, Sales, InvoiceNo

    abc, 15-01-2012, 100, 1

    abc, 15-02-2012, 50, 2

    abc, 20-02-2012, 50, 3

    abc, 15-03-2012, 75, 4

    abc, 10-04-2012, 125, 5

    abc, 15-04-2012, 125, 6

    abc, 05-05-2012, 20, 7

    abc, 25-05-2012, 100, 8

];

I need a table when the Sales meet the AsOfdate, in this case the table is as follows:

    Cust, Balance, BalDate, SalesDate1, SalesDate2

    abc, 100, 31-01-2012, 15-01-2012, 15-01-2012

    abc, 200, 29-02-2012, 15-01-2012, 15-01-2012

    abc, 150, 31-03-2012, 15-02-2012, 15-01-2012

    abc, 300, 30-04-2012, 10-04-2012, 15-03-2012

    abc, 400, 30-05-2012, 10-04-2012, 15-03-2012

I already solve using two For... Next, but it is very slow whe you put in the real world with a amount of data. Please John, take a moment and see some solution for me.  Thanks.

[johnw]

I'm very busy preparing a major installation, and won't be able to look at this. Sorry about that. Hopefully someone else can help.

[#11]

Can you please help me?

borja2012