Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

viveksingh
Contributor II

calculate MTD using set analysis

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

14 Replies
MVP
MVP

Re: calculate MTD using set analysis

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

viveksingh
Contributor II

Re: calculate MTD using set analysis

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.

MVP
MVP

Re: calculate MTD using set analysis

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
Contributor II

Re: calculate MTD using set analysis

Thanks for the reply sunny.

I don't have format for my field EFFECTIVE_DATE.

I have like below.

effectivedate.PNG   

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

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

MVP
MVP

Re: calculate MTD using set analysis

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
Contributor II

Re: calculate MTD using set analysis

This results nothingmoney.PNG

MVP
MVP

Re: calculate MTD using set analysis

Would you be able to share a sample?

viveksingh
Contributor II

Re: calculate MTD using set analysis

Attached is sample data with out LEDGER_NAME.

MVP
MVP

Re: calculate MTD using set analysis

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)')

Community Browser