Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
Partner
Partner

This Year MTD v Last Year MTD

I have a couple of expressions to do comparisons between periods. However, I am not able to get the previous year, MTD to work.


Currently I am using the below expressions for:

For Current Year MTD:  /*This returns for the current month to the current date - 2017-07-01 > 2017-07-24*/

Sum({<Year=,Month=,Date={">=$(=MonthStart(max(Date),0))<=$(=AddYears(max(Date),0))"} >} Float)

For Current Year YTD:

Sum({<Year=,Month=,Date={">=$(=YearStart(max(Date),0))<=$(=AddYears(max(Date),0))"} >} Float)


For Previous Year YTD:

Sum({<Year=,Month=,Date={">=$(=YearStart(max(Date),-1))<=$(=AddYears(max(Date),-1))"} >} Float)

For Previous Year MTD:  /*This needs to return for the current month, last year, to the current date - 2016-07-01 > 2016-07-24*/

???

I would appreciate assistance on this one.


Thank you in advance.


1 Solution

Accepted Solutions

Re: This Year MTD v Last Year MTD

May be this?

Sum({<Year=,Month=,Date={">=$(=AddYears(MonthStart(max(Date)),-1)) <=$(=AddYears(max(Date),-1))"} >} Float)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

6 Replies

Re: This Year MTD v Last Year MTD

May be this?

Sum({<Year=,Month=,Date={">=$(=AddYears(MonthStart(max(Date)),-1)) <=$(=AddYears(max(Date),-1))"} >} Float)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

Highlighted
New Contributor III

Re: This Year MTD v Last Year MTD

Current Year MTD: Sum (

{$<[FINANACIAL YEAR]=

    {">=$(=yearstart(today(),0,7))<$(=yearend(today(),0,2)),<=$(=yearstart(today(),7))<$(=yearend(today(),2))"}

    ,[Month]={"*"}>} MEASURE)

Previous Year MTD: Sum (

{$<[FINANACIAL YEAR]=

    {">=$(=yearstart(today(),-1,7))<$(=yearend(today(),0,2)),<=$(=yearstart(today(),0,7))<$(=yearend(today(),2))"}

    ,[Month]={"*"}>} MEASURE)

Highlighted
Valued Contributor III

Re: This Year MTD v Last Year MTD

Hello John,

Below is sample expression:

//Prev. Year MTD

Sum({<Year=, Month=, Date={">=$(=MonthStart(AddYears(Max(Date),-1)))<=$(=MonthEnd(AddYears(Max(Date),-1)))"}>}Float)

Regards!

Rahul Pawar

Highlighted
Valued Contributor II

Re: This Year MTD v Last Year MTD

Highlighted
Partner
Partner

Re: This Year MTD v Last Year MTD

Hello Rahul.

Thank you for replying.

I marked your solution as Helpful because I may be able to use it elsewhere.

Your "Sum({<Year=, Month=, Date={">=$(=MonthStart(AddYears(Max(Date),-1)))<=$(=MonthEnd(AddYears(Max(Date),-1)))"}>}Float)" Monthend takes it to the end of the month whereas I want it to date.

Thank you.

Highlighted
Partner
Partner

Re: This Year MTD v Last Year MTD

Hello Anil.

Spot on. Thank you very much. This works perfectly.