Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
inescastelhano
Partner - Creator II
Partner - Creator II

sum if / sum aggr

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

11 Replies
sunny_talwar

Try this may be:

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

inescastelhano
Partner - Creator II
Partner - Creator II
Author

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.

sunny_talwar

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

inescastelhano
Partner - Creator II
Partner - Creator II
Author

Hi,

I have it already and still nothing...

aggr.png

inescastelhano
Partner - Creator II
Partner - Creator II
Author

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.

sunny_talwar

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

Does it give you nulls still?

inescastelhano
Partner - Creator II
Partner - Creator II
Author

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

sunny_talwar

Do you have a sample I can look at?

inescastelhano
Partner - Creator II
Partner - Creator II
Author

Sure.

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

Thanks aggr2.png