# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

## 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?

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

## 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

Contributor II

## Re: calculate MTD using set analysis

I don't have format for my field EFFECTIVE_DATE.

I have like below.

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

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

Contributor II

## Re: calculate MTD using set analysis

This results nothing

MVP

## Re: calculate MTD using set analysis

Would you be able to share a sample?

Contributor II

## Re: calculate MTD using set analysis

Attached is sample data with out LEDGER_NAME.

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