calculate MTD using set analysis

[viveksingh]

Hi everyone,

I have an MTD calculation, which is like below.

InMonthToDate(EFFECTIVE_DATE,[Full Date],0)

I want to write the above one in setanalysis. 

Can any one help me on this.

Thanks,

Vivek

[sunny_talwar]

What is the expression and do you need MTD from the max selected date or MTD for today's month?

[viveksingh]

I have expression like,

=money(sum(if(JE_NATURAL_ACCOUNT >= 400000 and JE_NATURAL_ACCOUNT < 500000 and LEDGER_NAME='AMRI Consolidation Ledger' and InMonthToDate(EFFECTIVE_DATE,[Full Date],0),JEAMOUNT)),'$#,##0;($#,##0)')

I want to write the above in setanalysis.

[sunny_talwar]

May be this

=Money(Sum({<JE_NATURAL_ACCOUNT = {">=400000<500000"}, LEDGER_NAME = {'AMRI Consolidation Ledger'}, EFFECTIVE_DATE = {">=$(=Date(MonthStart(Max(EFFECTIVE_DATE)), 'DateFieldFormatHere'))<=$(=Date(Max(EFFECTIVE_DATE), 'DateFieldFormatHere'))"}>} JEAMOUNT), '$#,##0;($#,##0)')

Here replace DateFieldFormatHere with the format of your EFFECTIVE_DATE date field

[viveksingh]

Thanks for the reply sunny.

I don't have format for my field EFFECTIVE_DATE.

I have like below.

   

I don;t have access to modify the data model.

can you help me in formating the date in the expression itself?

[sunny_talwar]

Then may be this

=Money(Sum({<JE_NATURAL_ACCOUNT = {">=400000<500000"}, LEDGER_NAME = {'AMRI Consolidation Ledger'}, EFFECTIVE_DATE = {">=$(=Num(MonthStart(Max(EFFECTIVE_DATE))))<=$(=Num(Max(EFFECTIVE_DATE)))"}>} JEAMOUNT), '$#,##0;($#,##0)')

[viveksingh]

This results nothing

[sunny_talwar]

Would you be able to share a sample?

[viveksingh]

Attached is sample data with out LEDGER_NAME.

[sunny_talwar]

This works for the sample

=Money(Sum({<JE_NATURAL_ACCOUNT = {">=400000<500000"}, EFFECTIVE_DATE = {">=$(=Num(MonthStart(Max({<JE_NATURAL_ACCOUNT = {'>=400000<500000'}>}EFFECTIVE_DATE))))<=$(=Num(Max({<JE_NATURAL_ACCOUNT = {'>=400000<500000'}>}EFFECTIVE_DATE)))"}>} JEAMOUNT), '$#,##0;($#,##0)')

for your original app, try this

=Money(Sum({<JE_NATURAL_ACCOUNT = {">=400000<500000"}, LEDGER_NAME = {'AMRI Consolidation Ledger'}, EFFECTIVE_DATE = {">=$(=Num(MonthStart(Max({<JE_NATURAL_ACCOUNT = {'>=400000<500000'}, LEDGER_NAME = {'AMRI Consolidation Ledger'}>} EFFECTIVE_DATE))))<=$(=Num(Max({<JE_NATURAL_ACCOUNT = {'>=400000<500000'}, LEDGER_NAME = {'AMRI Consolidation Ledger'}>} EFFECTIVE_DATE)))"}>} JEAMOUNT), '$#,##0;($#,##0)')