Help needed in building a formula for MTD with today() function

[fashid]

Hi,

I am creating MTD YTD values using the below expression

=Count({<CalendarDate={">=$(=(MonthStart(today()))<=$(=(today())))"}>}DISTINCT  ENQUIRY_ID)

but i am getting zero result.

I need to use today function only as i have inconsistent dates in the application.

Could anybody help me out in this

Regards,

Nadeem

[PrashantSangle]

Hi,

Your expression is ok,

Just check date format.

Regards

[Anonymous]

hi yes max dreamer is right,

you have written correct statement

just check CalendarDate format

also try using

ount({<num(CalendarDate)={">=$(=(num(MonthStart(today())))<=$(=(num(today()))))"}>}DISTINCT  ENQUIRY_ID)

lets see if it works.

Thanks

BKC

[PrashantSangle]

Hi,

I think In set Analysis you can not convert the comparing field

Just check num(CalendarDate) Will not work in set analysis.

Regards

[#5]

=(count( {$<CalendarDatenum={">=$(=(MTDStartDate))<=$(=(MTDEndDate))"}>}  distinct ENQUIRY_ID))

MTDStartDate==num(MonthStart(today()))

MTDEndDate==num(today())

[Anonymous]

yes you are right my bad ..

Thank you Max

[ankitaag · Accepted solution]

Hey,

Try

=Count({<CalendarDate={">=$(=vMonthStart)<=$(=vMonthEnd)"}>}DISTINCT  ENQUIRY_ID)

create 2 variables

vMonthStart ==Date(MonthStart(today()),'DD-MM-YYYY')

vMonthEnd= =Date(today(),'DD-MM-YYYY')

Use the same dateformat as of your CalendarDate

Thanks and Regards,

Ankita

[fashid]

Wow thanks guys for showing different methods  .

Now i do not understand which answer do i mark as correct

[ankitaag]

You need to mark 1 answer as correct so as to close this discussion.

[satishkurra]

See if this can help you..

Load the below:

LOAD * Inline [

Date,Product,Sales

01/01/2014,Apple,100

02/01/2014,Samsung,200

03/01/2014,Nokia,900

04/01/2014,Apple,100

05/01/2014,Samsung,200

06/01/2014,Nokia,900

01/01/2015,Samsung,300

02/01/2015,Apple,400

03/01/2015,Nokia,300

03/05/2015,Apple,400

07/01/2015,Samsung,300

08/01/2015,Apple,400

09/01/2015,Nokia,300

10/05/2015,Apple,400 ];

Create straight tables like below

1. No dimension

2. Expressions as below

YTD and YTD -1

YTD: Sum({$<Date={">=$(=Num(YearStart(Max(Date))))<=$(=Max(Date))"}>}Sales)

YTD-1: SUm({$<Date={">=$(=Num(YearStart(Max(Date),-1)))<=$(=AddYears(Max(Date),-1))"}>}Sales)

QTD and QTD -1

QTD: Sum({$<Date={">=$(=Num(QuarterStart(Max(Date))))<=$(=Max(Date))"}>}Sales)

QTD-1 : SUm({$<Date={">=$(=Num(QuarterStart(Max(Date),-4)))<=$(=AddYears(Max(Date),-1))"}>}Sales)

WTD and WTD -1

WTD: Sum({$<Date={">=$(=Num(WeekStart(Max(Date))))<=$(=Max(Date))"}>}Sales)

WTD -1: SUm({$<Date={">=$(=Num(WeekStart(Max(Date),-52)))<=$(=AddYears(Max(Date),-1))"}>}Sales)

MTD and MTD -1

MTD: Sum({$<Date={">=$(=Num(MonthStart(Max(Date))))<=$(=Max(Date))"}>}Sales)

MTD -1: SUm({$<Date={">=$(=Num(MonthStart(Max(Date),-12)))<=$(=AddYears(Max(Date),-1))"}>}Sales)

Thanks

Satish