topic aggr() + aggr() in QlikView

aggr() + aggr()

Wed, 04 Apr 2012 21:34:41 GMT

I have an expression - exp1 that uses normdist() and aggr()

I have another expression - exp2 that also uses normdist() and aggr()

when I add exp1+exp2 i do not get any result in chart (straight table).

however if I add aggr like aggr(exp1+exp2) then correct value is displayed.

Is it a must to add aggr when both the expressions have aggr?

since I have further calculations to make, if I add aggr() at every step, then calculation takes a longer time.

Any comments?

aggr() + aggr()

Wed, 04 Apr 2012 21:47:36 GMT

If it is using the same type of aggr for every calculation you should be ok using one. If you need to change what fields you are aggr on you will need to use multiple

aggr() + aggr()

Wed, 04 Apr 2012 21:50:09 GMT

Am always aggregating on the same (single) field which is my dimension for the chart (straight table).

even I thought that I dont have to, but unless I aggregate am not getting the values to display, I see only a null. When I add aggr then I see correct value.

aggr() + aggr()

Wed, 04 Apr 2012 21:52:15 GMT

What is your expression?

aggr() + aggr()

Wed, 04 Apr 2012 21:59:20 GMT

Below is my expression after adding aggr and adding both parts

part1 -

(AGGR(($(DS_mu1)*(NORMDIST((($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))),0,1))

+$(DS_mu2)*(NORMDIST((-($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))),0,1))),ASOFSNAPSHOTDATE)

part2 -

AGGR(( SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))

((1/SQRT(2*(3.1415)))*((EXP(-(($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2)))*(($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))))/2)))),ASOFSNAPSHOTDATE)

part1+part2 =

(AGGR(AGGR(($(DS_mu1)*(NORMDIST((($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))),0,1))

+$(DS_mu2)*(NORMDIST((-($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))),0,1))),ASOFSNAPSHOTDATE)

AGGR(( SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))

((1/SQRT(2*(3.1415)))*((EXP(-(($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2)))*(($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))))/2)))),ASOFSNAPSHOTDATE),ASOFSNAPSHOTDATE))

Re: aggr() + aggr()

Wed, 04 Apr 2012 22:03:59 GMT

And this did not work?

AGGR(($(DS_mu1)*(NORMDIST((($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))),0,1))
+$(DS_mu2)*(NORMDIST((-($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))),0,1)))
+
( SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))
*
((1/SQRT(2*(3.1415)))*((EXP(-(($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2)))*(($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))))/2)))),ASOFSNAPSHOTDATE)

You could also try putting a sum around it and see what it returns:

SUM(AGGR(($(DS_mu1)*(NORMDIST((($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))),0,1))
+$(DS_mu2)*(NORMDIST((-($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))),0,1)))
+
( SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))
*
((1/SQRT(2*(3.1415)))*((EXP(-(($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2)))*(($(DS_mu1)-$(DS_mu2))/SQRT($(DS_sd1)*$(DS_sd1)+$(DS_sd2)*$(DS_sd2))))/2)))),ASOFSNAPSHOTDATE))

aggr() + aggr()

Thu, 05 Apr 2012 00:15:33 GMT

aggr returns a table of values. To add two aggr()'s you will either need to count, sum, max, min etc to get a single value and then add them together.

aggr() + aggr()

Thu, 05 Apr 2012 15:24:23 GMT

The first one gave me a null, and the second one gave me 0 as results.

aggr() + aggr()

Thu, 05 Apr 2012 15:25:22 GMT

Adding sum around each of the expressions and then aggr them also did not work got a null as value.

am wondering what could be causing this