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 with if or countif

Hi,

I am trying to use Aggr with if or countif but no luck. Can anyone please help in understanding this with an example?

Thanks

Shivesh

1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

add the criteria to your Expression in Set analysis

example

=sum(  {<aq_date = {"<=$(=date(today(),'DateFormatFor_aq_date'))"} >}  whatever)

or

in dimension  Try

AGGR(if(Max (TOTAL DATE (aq_date)) <= today()),aq_date),aq_date)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

9 Replies
sunny_talwar

What are you trying to do? Can you share a sample?

Chanty4u
MVP
MVP

just simple example:

=count(if([Unique ID]<>'NULL',[Unique ID]))



vinieme12
Champion III
Champion III

Example:

Sum(

          Aggr(

                    If(YOURCONDITION  , <THEN>,<ELSE>),

                    ,field1,field.....)

              )

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

Hi everyone,

For example I have a date field in a table called aq_date..Now to calculate "last aq_date", I've created a measure

Max (DATE (aq_date))

Now I want to filter out "Max (DATE (ttr_travel_date)) "which is less than or equal to current date.

I am trying this.....if(Max (DATE (ttr_travel_date)) <= today() which is incorrect.

Please help!

Thanks

Shivesh

vinieme12
Champion III
Champion III

if(Max (TOTAL DATE (ttr_travel_date)) <= today()

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

Thanks Vineeth for such a quick reply! but its returning null values. Let me explain the complete scenario:

I am trying to make an exportable table having various fields. Out of such fields there's one field where I require "Max (DATE (aq_date))" less than or equal to current date.

What should be the dimension/measure I should input?

I tried if(Max (TOTAL DATE (aq_date)) <= today()),aq_date)

Thanks in advance

vinieme12
Champion III
Champion III

add the criteria to your Expression in Set analysis

example

=sum(  {<aq_date = {"<=$(=date(today(),'DateFormatFor_aq_date'))"} >}  whatever)

or

in dimension  Try

AGGR(if(Max (TOTAL DATE (aq_date)) <= today()),aq_date),aq_date)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

Thanks a ton Vineeth!!

I removed total in your suggested dimension and it worked.

AGGR(if(Max ( DATE (aq_date)) <= today()),aq_date),aq_date)

vinieme12
Champion III
Champion III

if you didn't needed TOTAL; then you won't need Max as well

just try

AGGR(if( DATE (aq_date) <= today(),aq_date),aq_date)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.