Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
viveksingh
Creator III
Creator III

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
sunny_talwar

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

viveksingh
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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?

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
Creator III
Creator III
Author

This results nothingmoney.PNG

sunny_talwar

Would you be able to share a sample?

viveksingh
Creator III
Creator III
Author

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