topic Re: sum if / sum aggr in QlikView

sum if / sum aggr

inescastelhano — Wed, 24 Jun 2015 15:00:03 GMT

Hi everyone,

I have the following expressions, both work fine separately. Now I need to subtract them, and have the equivalent to "sum of rows" that one can have in straight table. I think I should use aggregate, but it returns null when I use it. Could someone help me please?

Expression 1:

(min({<myYear=,myMonth=,myDay=,myDate=,[Date 1]={'<$(=date(min(myDate)))'},[Date 2]={'>=$(=date(min(myDate)))<=$(=date(max(myDate)))'}>}[Date 2])

(could also be achieved with max, sum or only)

Expression 2:

-(min(total{$}

([Data])))

Final expression:

sum(aggr(Expression 1 - Expression 2 , ID))

This final expression returns null.

Thanks in advance.

Inês

Re: sum if / sum aggr

sunny_talwar — Wed, 24 Jun 2015 15:02:53 GMT

Try this may be:

Sum(Aggr(RangeSum(Expression1, -Expression2), ID))

Re: sum if / sum aggr

inescastelhano — Wed, 24 Jun 2015 15:10:53 GMT

Hi sunindia,

Thanks for your reply. It works on the RangeSum level, but unfortunately, when I use the aggr function it returns null anyway.

I don't understand what I am doing wrong, since the expressions work fine separately.

Re: sum if / sum aggr

sunny_talwar — Wed, 24 Jun 2015 15:19:04 GMT

Create a straight table with ID as dimension and RangeSum(Expression1, -Expression2) as Expression and see if it gives you numeric value for ID's

Re: sum if / sum aggr

inescastelhano — Wed, 24 Jun 2015 15:31:25 GMT

Hi,

I have it already and still nothing...

Re: sum if / sum aggr

inescastelhano — Wed, 24 Jun 2015 15:33:06 GMT

The first column is the ID, the second one is the correct value for each ID, but the sum is incorrect. However, if I use "sum of rows" instead of "expression total", it works.

Re: sum if / sum aggr

sunny_talwar — Wed, 24 Jun 2015 15:35:20 GMT

For your second expression, can you add Sum next to Aggr like this: Sum(Aggr(....

Does it give you nulls still?

Re: sum if / sum aggr

inescastelhano — Wed, 24 Jun 2015 15:43:35 GMT

Since the aggr returns null, the sum (aggr (...)) returns zero

Re: sum if / sum aggr

sunny_talwar — Wed, 24 Jun 2015 15:45:23 GMT

Do you have a sample I can look at?

Re: sum if / sum aggr

inescastelhano — Wed, 24 Jun 2015 16:14:31 GMT

Sure.

Here it follows. I am selecting the entire month of january, 2015, as it is in the image.

Thanks

Re: sum if / sum aggr

sunny_talwar — Wed, 24 Jun 2015 16:27:51 GMT

This expression seems to be working:

=Sum({<myYear=,myMonth=,myDay=,myDate= >} Aggr(Min({<myYear=,myMonth=,myDay=,myDate=,[Date 1]={'<$(=date(min(myDate)))'},[Date 2]={'>=$(=date(min(myDate)))<=$(=date(max(myDate)))'}>}[Date 2])-Min(TOTAL [myDate]), [Nº de Episódio]))

Re: sum if / sum aggr

inescastelhano — Wed, 24 Jun 2015 16:36:58 GMT

Oh my God.

I have been working on this for days... And now it works!! Thank you so much!

All the best,

Inês