topic Re: Calculate YTD starting from April in QlikView

Calculate YTD starting from April

sifatnabil — Wed, 09 Apr 2014 02:56:29 GMT

Hi all,

Usually YTD calculations will pick the range from January to current date. But how can I modify it to start from April instead? e.g. if it's 1st June 2013 today, YTD will be sum from April 1st 2013 to 1st June 2013.

I know the MTD in set analysis is this:

sum({<date=,Year={$(=Max(Year))},Month={"=$(=max(Month))"}>} sales)

Could I get the YTD format and also make it start from April? Thanks a lot.

Re: Calculate YTD starting from April

Wed, 09 Apr 2014 03:07:41 GMT

Try this,

sum({<date=,Year={$(=Max(Year))},Month={">=$(=MinMonth)"}>} sales)

Suraj

Re: Calculate YTD starting from April

sifatnabil — Wed, 09 Apr 2014 03:12:01 GMT

Thanks Suraj - what is MinMonth? Do I need to define it somewhere?

Re: Calculate YTD starting from April

SergeyMak — Wed, 09 Apr 2014 03:31:30 GMT

You should add to the load script two statements which define your new year and than you will be able to operate them as usual.

year(yearstart(Date(Date),1,3)) as [Fiscal Year],

if(num(Month(Date(Date)))<4, num(Month(Date(Date)))+9,num(Month(Date(Date)))-3) as [Fiscal Period]

Re: Calculate YTD starting from April

Wed, 09 Apr 2014 04:26:47 GMT

Yepp. It is a variable and you can define whatever you want.

Suraj

Re: Calculate YTD starting from April

Wed, 09 Apr 2014 04:29:34 GMT

Try this

Your calender should be financial calender like this

Year(AddMonths(DateField,9)) AS Year

Month(DateField) AS Month

Num(Month(AddMonths(DateField,9))) AS MonthNum

Create variables

vMaxYear : = Max(Year)

vMaxMonth : = IF(Year=$(vMaxYear),Max(MonthNum))

This is your expression:

=sum({<Year={$(vMaxYear)},MonthNum={"<=$(vMaxMonthNum)"}>} sales)

Re: Calculate YTD starting from April

Wed, 09 Apr 2014 04:29:49 GMT

I think you should explore the InYeartoDate function. It has an option to say when the financial year starts.

if inyeartodate(date,yearstart(today()),0,4), <your expression>.

Re: Calculate YTD starting from April

Wed, 09 Apr 2014 06:12:54 GMT

Hi Sifat, Attaching a simple example. Hope you find it useful.

Thanks,

Ram

Re: Calculate YTD starting from April

jagan — Wed, 09 Apr 2014 06:28:46 GMT

Hi,

Try this expression

=sum({<Month=, Year=, date={'>=$(=YearStart(Today(), 0, 4))<=$(=Today())'}>} sales)

If it not works then check the date format of date and Today() values, if it is not same then format the date.

Regards,

Jagan.

Re: Calculate YTD starting from April

sifatnabil — Thu, 10 Apr 2014 03:27:54 GMT

Thanks, this worked!