Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
sifatnabil
Specialist
Specialist

Calculate YTD starting from April

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.


1 Solution

Accepted Solutions
Not applicable

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

Thanks,

Ram


View solution in original post

9 Replies
Not applicable

Try this,


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


Suraj

sifatnabil
Specialist
Specialist
Author

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

SergeyMak
Partner Ambassador
Partner Ambassador

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]

Regards,
Sergey
Not applicable

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

Suraj

Not applicable

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) 

Not applicable

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>.

Not applicable

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

Thanks,

Ram


jagan
Luminary Alumni
Luminary Alumni

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.

sifatnabil
Specialist
Specialist
Author

Thanks, this worked!