Time period

[#1]

Hello everyone,

I am looking to create a gauge showing how many actions I have that are due by the end of the month and struggling with the expression. Looking for an expression that would have :

if([Target date]>Today() and [Target date]<Monthend, count(Actions))

Not quite sure how to formulate it.

Thank you

Anna

[alexandros17]

Count(if([Target date]>Today() and [Target date]<Monthend,Actions,0)

let me know

[its_anandrjs]

Try with

Count({<[Target date]  = { '>$(=Today( )), [Target date] = {'<$(=Monthend)'} >} Actions)

[Anonymous]

Hi Neshina,

Try like this

=Count(if(date([Targetdate],'DD/MM/YYYY')>=Date(Today(),'DD/MM/YYYY') and Date([Targetdate],'DD/MM/YYYY')<=Date(MonthEnd,'DD/MM/YYYY'),Actions))

[Anonymous]

Alessandro Saccone,

I bet it is just a mistyping, having 0 and ')' on the same key...  And I usually utilize distinct with count:

Count(distinct if([Target date]>Today() and [Target date]<=Monthend, Actions))

[alexandros17]

The mistake is the missing parenthesis,

Count

     (

          if(

               [Target date]>Today() and [Target date]<Monthend

                    ,Actions

                    ,0

          )

     )

This expression count "Actions" when condition is true otherwise count 0, if you use Distinct and you have the same Action I don't know if it is correct to Discharge

[its_anandrjs · Accepted solution]

Hi Anna,

You can try this also

Count ( {<[Target date]  = {">$(=Today()) <$(=Monthend(Today()))"} >}  Actions )

Or

Count({<[Target date]  = { '>$(=Today( )) <$(=Monthend)'} >} Actions)

[Anonymous]

Alessandro,

It is not sum(), it is count()...  The 0-value is a value too, and will be counted.  Just try this in a text box:
=count(0)

[alexandros17]

You're right ...

[Anonymous]

So, we agree it was a mistyping