topic Re: Aggr and {1} - no selection in QlikView

Aggr and {1} - no selection

felix18807 — Mon, 18 Mar 2013 16:51:43 GMT

Hi I'm trying to make an expression run ignoring all selections. I have created this expression (see below) to deduce time spent traveling but it is being affected by selections.

Can someone tell me where I'm going wrong?

SUM({1} AGGR({1} MRDuration,TravelLeg))

Re: Aggr and {1} - no selection

Anonymous — Mon, 18 Mar 2013 16:57:35 GMT

Not clear what aggr is doing here. Try this

SUM({1} MRDuration)

Re: Aggr and {1} - no selection

hic — Mon, 18 Mar 2013 16:58:10 GMT

You should probably not use Aggr() at all.

Try Sum({1} MRDuration).

HIC

Re: Aggr and {1} - no selection

Anonymous — Mon, 18 Mar 2013 17:00:04 GMT

This probably makes some sense, depending on what is to calcualte:

sum({1} aggr(SUM({1} MRDuration), TravelLeg))

Re: Aggr and {1} - no selection

hic — Mon, 18 Mar 2013 17:18:29 GMT

Michael

Hypothetically, your 2nd formula may make sense, yes.

But only if one MRDuration can belong to several TravelLegs. If one record of MRDuration only can belong to one value of TravelLeg, then the Aggr() is redundant.

HIC

Re: Aggr and {1} - no selection

Anonymous — Mon, 18 Mar 2013 17:23:47 GMT

Henric,

Absolutely agree. Just tying to keep it working even with aggr() - which is most likley redundant here.

Re: Aggr and {1} - no selection

Mon, 18 Mar 2013 17:44:18 GMT

can you attach ur qvw file?

Re: Aggr and {1} - no selection

felix18807 — Tue, 19 Mar 2013 08:49:22 GMT

I wish I could circumvent the aggr but what I have is a table of travel durations based on pairs of post codes and a table of stages of journey (per job) linked by a postcodepair key.

What I am looking to do on the dashboard is provide a total distance travelled but if I use Sum({1} MRDuration) I will get the sum of all the distances held in the distances table not the total distance travelled during all the jobs.

I hope I'm making sense...

Re: Aggr and {1} - no selection

hic — Tue, 19 Mar 2013 09:21:02 GMT

Since I do not have the data, I cannot help you.

But if a distance (MRDuration) can belong to/be used by several TravelLegs, then you should use the Aggr() function just like Michael Solomovich suggests:

sum({1} aggr(SUM({1} MRDuration), TravelLeg))

HIC

Re: Aggr and {1} - no selection

felix18807 — Tue, 19 Mar 2013 10:28:15 GMT

Thanks guys I believe that has resolved my issue.