14 Replies Latest reply: Sep 26, 2017 5:11 AM by Rajan Kasbe

# 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

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

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

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

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

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

• ###### Re: calculate MTD using set analysis

This results nothing

• ###### Re: calculate MTD using set analysis

Would you be able to share a sample?

• ###### Re: calculate MTD using set analysis

Attached is sample data with out LEDGER_NAME.

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

• ###### Re: calculate MTD using set analysis

but the data showing is different

OLD expression:

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

output : \$945,254,038

new expression with set analysis:

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

output: \$45,555,397

• ###### Re: calculate MTD using set analysis

I am not sure what Full Date is.... that is the only missing piece I am not sure about

• ###### Re: calculate MTD using set analysis

Hi sunny,

Full Date is holds data in date format.

Attached is data for Full Date

I'm not sure about Inmonthtodate funtion

• ###### Re: calculate MTD using set analysis

Do you have a selection in Full Date when you look at this expression? Also, for InMonthToDate() did you look at the help section as to what it is doing

inmonthtodate ‒ QlikView

• ###### Re: calculate MTD using set analysis

Hi Vivek,

MTD using set analysis.

Sum({<Year=, Month=, Quarter=, DateNum={">=\$(=Num(MonthStart(Max(DateNum))))<=\$(=Max(DateNum))"}>} Sales)

DateNum is the OrderDate field

Thanks,

Rajan