Previous year MTD

[#1]

Hi all,

I am using the following expression for calculating previous year MTD and previous year YTD

Previous MTD : num(Sum({<VISIT_DATE={'>=$(=MonthStart(MakeDate(Year(VISIT_DATE)-1,Month(VISIT_DATE),Day(VISIT_DATE))))

  <=$(=MakeDate(Year(VISIT_DATE)-1,Month(VISIT_DATE),Day(VISIT_DATE)))'}>}NET_AMOUNT)/vFactor,'##.##')

Previous YTD:

=num(Sum({<VISIT_DATE={'>=$(=yearstart(MakeDate(Year(VISIT_DATE)-1,Month(VISIT_DATE),Day(VISIT_DATE))))

  <=$(=MakeDate(Year(VISIT_DATE)-1,Month(VISIT_DATE),Day(VISIT_DATE)))'}>}NET_AMOUNT)/vFactor,'##.##')

Previous YTD expression is working fine.

But  for previous MTD expression I am getting Previous YTD value.

Can anyone tell me where I went wrong or Is there any other better way to calculate previous year MTD.

(note: I dont want to use master calendar)

[Anonymous]

to get previous month you need to use addmonths(VISIT_DATE,-1) to get one month earlier

do you want previous month of same year

Previous MTD: num(Sum({<VISI_DATE={'>=$(=Monthstart(MakeDate(Year(VISIT_DATE),Month(addmonths(VISIT_DATE,-1),Day(VISIT_DATE)))

if previous year, previous month than year(Visit_Date)-1

[#3]

Thanks for your reply

I want same month for previous year

Eg: Current MTD - Jun 2014

     Previous MTD - Jun 2013

same month for previous year

[PrashantSangle]

Hi,

find below expression

Previous Year MTD

num(Sum({<VISIT_DATE={'>=$(=MonthStart(MakeDate(Year(VISIT_DATE)-1,Month(VISIT_DATE),Day(VISIT_DATE))))

  <=$(=MonthEnd(MakeDate(Year(VISIT_DATE)-1,Month(VISIT_DATE),Day(VISIT_DATE))))'}>}NET_AMOUNT)/vFactor,'##.##')

You are Missing MonthEnd()

Regards

[#5]

I want to calculate till the selected date only

For eg My date is 15-Aug-2014

I want to calculate for dates between 01-Aug-2013 to 15-Aug-2013

[ashfaq_haseeb]

Hi,

have a look at below post.

http://community.qlik.com/docs/DOC-7045

Regards

ASHFAQ

[Anonymous]

can you give examplem, you said that you get for Previous MTD the same result as for Previous YTD

according to your expressions Previous YTD would be result from 1.1.2013 to 3.9.2013 (if VIST_DATE = today) and Previous MTD would be 1.9.2013 to 3.9.2013

try the following:

put expression for Previous MTD  to an Textbox (=num(Sum....) and VISIT_DATE in a listbox, then select one VISIT_DATE and look what the textbox is showing

and if it is not the wanted result try to change the expressions.

[#8]

Hi all,

Thanks all for your replies

My formula is working fine if I give the date by Inline script its not working when the date is loaded from qvd.

is it something bcoz of date format.

If so what can i do for that

[PrashantSangle]

Hi,

What type of format your date field contain in QVD.

Can You some sample date?

Regards

[MK_QSL · Accepted solution]

Vidhya... use below for your requirements.... Let me know if still not working for you..

MTD

=SUM({<Year = , Month = , TransactionDate = , TransactionDate = {'>=$(=MonthStart(Today()))<=$(=Today())'}>}Amount)

YTD

=SUM({<Year = , Month = , TransactionDate = , CalYear = {'$(=Year(Today()))'}>}Amount)

PYMTD

=SUM({<Year = , Month = , TransactionDate = , TransactionDate = {'>=$(=MonthStart(Today(),-12))<=$(=AddYears(Today(),-1))'}>}Amount)

PYYTD

=SUM({<Year = , Month = , TransactionDate = , CalYear = {'$(=Year(Today())-1)'}>}Amount)

Change your field names accordingly....

Instead of MTD, YTD from Today(), if you want user to select a data and want to have result from YTD to selected date or MTD for that date, change Today() in all my above formulas with Max(TransactionDate)