topic Re: AGGR: return values on one line in QlikView

AGGR: return values on one line

sibrulotte — Fri, 23 Jan 2015 22:00:53 GMT

How do I get values for every line?

I'm suppose to show for each "Portefeuille" on it's own, the total "Portefeuille" market value.

The values I get with the expression below are pretty accurate, but they are returned only for one line.

Total is as follows:

=sum(aggr(sum({1< VAL_RPT.VAL_DATE = p(Date_calcul), VAL_RPT.PORT_NO = {$(vR_OBL_PORT_NO)} >} VAL_RPT.MKT_VALUE) , VAL_RPT.PORT_CODE))

What am I doing wrong?

Re: AGGR: return values on one line

Anonymous — Sat, 24 Jan 2015 12:32:57 GMT

Hello Simon,

after dimension Emetteur I see there is another dimension: you can try to make tha aggregation even on this field

writing sum(aggr(sum(.....), VAL_RPT.PORT_CODE, ThirdDimension)).

If it is not correct, you had better to attach a sample qvw.

Re: AGGR: return values on one line

Sat, 24 Jan 2015 12:42:35 GMT

Hi Simon,

Try this:

=sum(aggr(NODISTINCT sum({1< VAL_RPT.VAL_DATE = p(Date_calcul), VAL_RPT.PORT_NO = {$(vR_OBL_PORT_NO)} >} VAL_RPT.MKT_VALUE) , VAL_RPT.PORT_CODE))

NODISTINCT should return a value for every record.

Best,

Matt

Re: AGGR: return values on one line

Anonymous — Sat, 24 Jan 2015 12:54:37 GMT

Hi,

=sum(aggr(sum({1< VAL_RPT.VAL_DATE = p(Date_calcul), VAL_RPT.PORT_NO = {$(vR_OBL_PORT_NO)} >} VAL_RPT.MKT_VALUE) , VAL_RPT.PORT_CODE))

the above expression is grouping on VAL_RPT.PORT_CODE,that's the reason its returning only for one line.

by default it will display for first record in the group.

Regards

Neetha

Re: AGGR: return values on one line

sibrulotte — Mon, 26 Jan 2015 14:00:03 GMT

Elena: there were dimensions, and I had tried taking them out before posting this, with no avail. I really need that figure to show the grouping on PORT_CODE...

Matthew: NODISTINCT returns a value for each line, but are insanely high figures that I can't comprehend 😕

BUT, it was the right answer after all, I just needed to take out the sum preceding the expression:

=aggr(NODISTINCT sum({1< VAL_RPT.VAL_DATE = p(Date_calcul), VAL_RPT.PORT_NO = {$(vR_OBL_PORT_NO)} >} VAL_RPT.MKT_VALUE) , VAL_RPT.PORT_CODE)

THANK YOU ALL!!!!!

I love this community.

Re: AGGR: return values on one line

yink-hpv — Thu, 11 Jan 2018 05:26:37 GMT

thanks Simon, very helpful