9 Replies Latest reply: Dec 21, 2016 10:11 AM by Vineeth Pujari

# 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

• ###### Re: Aggr with if or countif

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

• ###### Re: Aggr with if or countif

just simple example:

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

• ###### Re: Aggr with if or countif

Example:

Sum(

Aggr(

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

,field1,field.....)

)

• ###### Re: Aggr with if or countif

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.

Thanks

Shivesh

• ###### Re: Aggr with if or countif

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

• ###### Re: Aggr with if or countif

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)

• ###### Re: Aggr with if or countif

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)

• ###### Re: Aggr with if or countif

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)

• ###### Re: Aggr with if or countif

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)