12 Replies Latest reply: Jun 5, 2017 7:03 AM by Vineeth Pujari

# Closed on this month

Hi Experts,

I am using below formula to showcase numbers that are closed on this month:

CommonDateNum={">=\$(=Num(MonthStart(Max(CommonDateNum)))<=\$(=Max(CommonDateNum))"

But I can see that there are still some tickets that are not coming after applying this condition,i thought that "=" is not working in the below condition,so I added -1 in the expression to consider all values.

CommonDateNum={">=\$(=Num(MonthStart(Max(CommonDateNum))-1))<=\$(=Max(CommonDateNum)-1)"

Please let me know if this condition is fine or do I need to change something....

• ###### Re: Closed on this month

Can you try this -

CommonDateNum={">=\$(=Num(MonthStart(Max(CommonDateNum)))<=\$(=Floor(Num(MonthEnd(Today()))))"

Just to check - if chaning it a bit makes any difference.. there seems no problem with your expression.

• ###### Re: Closed on this month

Can you check if the format of date in missing items is just the date put them inside num() to see if you get any decimal part as well.

It is quite possible that those dates which are not falling under your range may have time part (which may not be visible because they might only set to show date and not time).

• ###### Re: Closed on this month

Example

if(num(month(<DateField>)) = Num(Month(Today())) , 1,0)  as CurrentMonthFlag

and use this flag in set analysis

CurrentMonthFlag = {1}

• ###### Re: Closed on this month

Hey, Vineet

Thanks a lot, it worked but

I think I missed out something in my requirement.

Its like,

I have to share the data for current month but from 1st to today()-1 on every single day.

ON 31st : i have to share data from 1st till 30th,that's why I am not making this flag in my script.

I need to validate the data from 1st to 30th May as well. but still there is some prob with my expression so need help.

• ###### Re: Closed on this month

what is the format for CommonDateNum?

is it an Integer? i don't see any reason why your current expression would not work

The flag can be simplified as below

FABS(InMonthToDate(<DATEFIELD>,today()-1,0)) as MTDFLAG,

• ###### Re: Closed on this month

Hi,

CommonDateNum is simply num(<my datefield>) .

• ###### Re: Closed on this month

do any of them have timestamp values? then you need floor(<Datefield>)

have you tried below?

FABS(InMonthToDate(<DATEFIELD>,today()-1,0)) as MTDFLAG,

• ###### Re: Closed on this month

Yes, i tried this flag but when I am selecting "1",it is selecting "MAY 2017".

Yes,my date field has both date and timestamp in it.,

• ###### Re: Closed on this month

then try as below

FABS(inmonthtodate(Timestamp#(<Datefield>,'DD/MM/YYYY H:mm:ss'),today()-1,0))  as MTDFLAG

Replace with your datefield format   'DD/MM/YYYY H:mm:ss'

• ###### Re: Closed on this month

Hi,

Make modification as per CommonDateNum Format.

Use Date() to format your date in set analysis

max(CommonDateNum) -----> returns date in numeric format.

For debugging your script test output of your expression in text object

create 2 text object 1 with expression

=Num(MonthStart(Max(CommonDateNum))-1)

2nd with

=Max(CommonDateNum)-1

Both output format match with you CommonDateNum format.

Regards,

Prashant

• ###### Re: Closed on this month

this should work

CommonDateNum={">=\$(=MonthStart(Max(CommonDateNum)))<=\$(=Max(CommonDateNum)-1)"}