Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

aggr() + aggr()

Hi

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?

8 Replies
Not applicable
Author

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

Not applicable
Author

Hi

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.

Not applicable
Author

What is your expression?

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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