Aggr with if or countif

[#1]

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

[sunny_talwar]

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

[Chanty4u]

just simple example:

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

[vinieme12]

Example:

Sum(

          Aggr(

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

                    ,field1,field.....)

              )

[#5]

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]

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

[#7]

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 · Accepted solution]

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)

[#9]

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]

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)