Count from inception to Last Day in Last Month

[Anonymous]

Hi all,

This expression below counts all ID  from the inception of the business

=COUNT({<Date={'<=$(vMaxDate)'},Year=,Month=>}DISTINCT ID)

I need help with the expression that counts all IDs from inception to the the last day in the previous month.

Thanks

[Gysbert_Wassenaar]

Try:

=COUNT({<Date={'<=$(=monthend($(vMaxDate),-1))'},Year=,Month=>}DISTINCT ID)

[Anonymous]

Hi,

The expression returns a null.

[Gysbert_Wassenaar]

Try =COUNT({<Date={'<=$(=num(monthend($(vMaxDate),-1)))'},Year=,Month=>}DISTINCT ID)

[Anonymous]

This =DATE(MonthEnd(vMaxDate)-1) returns 30/07/2014 assuming Year = 2014 and Month = JULY

The expression is to count all up to the last day in the last month eg. Year =2014 and Month = JUNE

Is this clear enough? Thanks

[tresesco]

You are probably missing an important comma and parenthesis in the expression. There is significant difference
between

monthend($(vMaxDate),-1)                // suggested by Gysbert

and

DATE(MonthEnd(vMaxDate)-1)         // your example

[Anonymous]

Thanks

=COUNT({<Date={'<=$((monthend($(vMaxDate),-1)))'},Year=,Month=>}DISTINCT ID) // from inception to la

=Count( {<Date={'<=$(vMaxDate)'},Year=,Month=,Week=,iDay=,Quarter=>}   DISTINCT(ID ))

Both these expressions return the same value,importantly I want all count on ID to Date and secondly count to last day in the last month.

[eduardo_sommer]

Try date(monthstart($(vMaxDate))-1)

This will take the first date of the current month and subtract 1.

Eduardo

[Anonymous]

Hi guys,

I finally had this expression rather which gave me the desire result

=Count (DISTINCT(if(Date <= vIncept_To_Last_Month,ID)))

WHERE vIncept_To_Last_Month is the Last day in the Last month

Thanks