topic Re: Previous year MTD in QlikView

Previous year MTD

Wed, 03 Sep 2014 06:12:10 GMT

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)

Re: Previous year MTD

Anonymous — Wed, 03 Sep 2014 06:18:12 GMT

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

Re: Previous year MTD

Wed, 03 Sep 2014 06:22:41 GMT

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

Re: Previous year MTD

PrashantSangle — Wed, 03 Sep 2014 06:24:15 GMT

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

Re: Previous year MTD

Wed, 03 Sep 2014 06:29:03 GMT

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

Re: Previous year MTD

ashfaq_haseeb — Wed, 03 Sep 2014 06:34:19 GMT

Hi,

have a look at below post.

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

Regards

ASHFAQ

Re: Previous year MTD

Anonymous — Wed, 03 Sep 2014 06:39:13 GMT

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.

Re: Previous year MTD

Wed, 03 Sep 2014 07:22:59 GMT

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

Re: Previous year MTD

PrashantSangle — Wed, 03 Sep 2014 07:58:04 GMT

Hi,

What type of format your date field contain in QVD.

Can You some sample date?

Regards

Re: Previous year MTD

MK_QSL — Wed, 03 Sep 2014 08:54:36 GMT

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)

Re: Previous year MTD

Sun, 07 Sep 2014 12:44:15 GMT

Hi Manish,

Thank you so much

Its working fine..