topic Re: AGGR Help in QlikView

AGGR Help

Fri, 27 Sep 2013 15:06:21 GMT

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 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

Re: AGGR Help

datanibbler — Fri, 27 Sep 2013 15:17:48 GMT

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

Re: AGGR Help

MK_QSL — Fri, 27 Sep 2013 15:21:36 GMT

Create a chart

with dimension

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

and expression

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

Re: AGGR Help

Fri, 27 Sep 2013 15:23:34 GMT

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?

Re: AGGR Help

jpapador — Fri, 27 Sep 2013 15:25:58 GMT

I have attached a QVW with the desired result.

Re: AGGR Help

tresB — Fri, 27 Sep 2013 15:28:13 GMT

PFA

Re: Re: AGGR Help

Fri, 27 Sep 2013 15:44:56 GMT

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

Re: AGGR Help

MK_QSL — Fri, 27 Sep 2013 15:50:39 GMT

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

Re: Re: Re: AGGR Help

tresB — Fri, 27 Sep 2013 15:51:41 GMT

PFA

Re: AGGR Help

jpapador — Fri, 27 Sep 2013 15:56:27 GMT

See attached QVW

Re: Re: Re: AGGR Help

spsrk_84 — Fri, 27 Sep 2013 17:04:51 GMT

Hi Steve,

I have written the expressin for which the Overspent is 900 for the Area A.

I have atatched the file please check if this works fine or not.

Thanks,

Shiva