calculate MTD using set analysis

[viveksingh]

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

[viveksingh]

appreciate your efforts sunny. able to see data with your expression.

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

[sunny_talwar]

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

[viveksingh]

Hi sunny,

Full Date is holds data in date format.

Attached is data for Full Date

I'm not sure about Inmonthtodate funtion

[sunny_talwar]

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

[Anonymous]

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