AGGR Help

[#1]

Hi All

AGGR help seems a common theme, so I hope someone can help me as I've not used AGGR before.  However, it's important to note I think aggregate is what I need, I may need a different bit of functionality.

Basically, I have a table, like that below;

Area      Grant     Actual     Budget

A          111          100         200

A          111          200         400

A          111          200         500

A          222         400         200

A          222         400         200

A          222         300         200

A          222         200         200

I want to display the value of overspend for grants by area.  So Grant 111 has not overspent, they have underspent by 600.  Whereas Grant 222 has overspent by 500.  This nets off at an underspend of 100.

However, my table should state

Area     Overspend

A          500

Can anyone offer me any guidance on how to do this please?

Many thanks

Steve

[datanibbler]

Hi Steve,

I don't have a very long history of working with QlikView myself, but if I understand you correctly, you don't need a complicated thing like AGGR - what you want is actually only to sum up those two fields, "Actual" and "Budget" (for a given area) and compare them and output the result? Is that right?

=> In that case, just aggregate the table, using SUM() - take care, you need a GROUP BY clause containing ALL other (non-aggregated) fields at the end of the LOAD statement, like this:

LOAD

       Area,

       Grant,

       SUM(Actual),

       SUM(Budget)

FROM ...

GROUP BY Area, Grant;

HTH

Best regards,

DataNibbler

[MK_QSL]

Create a chart

with dimension

=IF(Aggr(sum(Actual)-Sum(Budget),Area,Grant)>0,Area)

and expression

Aggr(sum(Actual)-Sum(Budget),Area,Grant)

[#4]

Thanks Data Nibbler

I had considered this however this cannot be solved in the script.  The above table is a simplified view, other fields include Month, Year, Cost Category etc.  Thus, to Group this data I would have to lose these fields which I cannot do as they are used within the table in other expressions (via set analysis).

Any other solutions?

[jpapador]

I have attached a QVW with the desired result.

[tresesco]

PFA

[#7]

Hi

That's close.  However, it did not work in my model and when I expanded your example I got errors.

I have attached a slightly amended example where the formula is not working.

If anyone can amend this so that the Over Spend chart states £900 (which is the overspend for 222 and 333), I would really appreciate it.

Thanks

Steve

[MK_QSL · Accepted solution]

Hi Steve,

Replace your dimensions by Calculated dimensions as below

=IF(Aggr(sum(Actual)-Sum(Budget),Area,Grant)>0,Grant)

and

=IF(Aggr(sum(Actual)-Sum(Budget),Area,Grant)>0,Grant)

and select Supress When Value is Null

[tresesco]

PFA

[jpapador]

See attached QVW