Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
What is the expression and do you need MTD from the max selected date or MTD for today's month?
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.
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
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?
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)')
This results nothing
Would you be able to share a sample?
Attached is sample data with out LEDGER_NAME.
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)')