Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

YTD till current day

hello i'm calculating YTD

i'm using the following in my expression

sum({$<Year = {$(=max(Year)-1)}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} LineSalesAmount)

this expression gives me YTD of lest year till the end of the month.I need to add the day till today.how can i add it in my expression??

thank you:)

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Hi layal,

ups, you got me. My expression will limit the evaluation to days of months < current day of months.

That's absolutely nonsense, of course.

I guess you have a date field, too. (if not, that could be created during load, see my example).

=sum({$<Year = {$(=year(today())-1)}, Month = {">=$(=min({<Year={$(=year(today())-1)}>} Month)+1) <=$(=max({<Year={$(=year(today()))}>} Month))"},Date={"<=$(=date(today()))"}>} Value)

should work, now.

Sorry for my mistake, I think I need some sleep,

Stefan

View solution in original post

13 Replies
swuehl
MVP
MVP

Hi layal,

assuming you have a "Day" field, try

sum({$<Year = {$(=max(Year)-1)}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}, Day = {"<=$(=day(today(2)))"}>} LineSalesAmount)

Regards,

Stefan

Not applicable
Author

with last YTD that's what i did but it's not giving me the result till from beginning of last year till same day as today this year!

sum({$<Year = {$(=max(Year-1))}, Month = {">=$(=min({<Year={$(=max(Year-1))}>} Month)+1) <=$(=max({<Year={$(=max(Year))}>} Month))"},Day={"<=$(=day(today()))"}>} SalesDetails.CSEXPR - SalesDetails.CSEXVT)/1500

any idea why??!

swuehl
MVP
MVP

Hi layal,

I am not sure what you want to achieve with that expression:

1) Do you really want "max(Year-1)" or "max(Year)-1" ?

2) Then, why do you limit the Month to the min value?

Regards,

Stefan

Not applicable
Author

hello stefan,

1) i need the max of last year

2) i'm choosing min to get the minimum month of the year

anyway to make it clear i need to get the Last YTD starting february till current date of today

any idea??

regards,

layal

Not applicable
Author

by the way the following expression is working,it's giving me the right result starting february last year but till the "END" of the same month of this year.and what i need is till the same day not till the same end of month

sum({$<Year = {$(=max(Year-1))}, Month = {">=$(=min({<Year={$(=max(Year-1))}>} Month)+1) <=$(=max({<Year={$(=max(Year))}>} Month))"}>} SalesDetails.CSEXPR - SalesDetails.CSEXVT)/1500

swuehl
MVP
MVP

Hi layal,

I tried to build a small .qvw

I think the calculation is correct to the current day last year. Please check.

Regards,

Stefan

swuehl
MVP
MVP

Hi layal, it's me again.

Please note that your expression is depending on current selection (previous year of max. selected year).

If you need a fixed last year as of today (i.e. 2010), you could use

=sum({$<Year = {$(=year(today())-1)}, Month = {">=$(=min({<Year={$(=year(today())-1)}>} Month)+1) <=$(=max({<Year={$(=year(today()))}>} Month))"},Day={"<=$(=day(today()))"}>} Value)

Is starting the YTD with last years min month +1 (Februar in this example, but depending on data) a business requirement?

Regards,

Stefan

Not applicable
Author

hello stefan,

i checked your qvw file.i used the same expression as yours but i'm not getting the right result regarding Last YTD.

it's as if it's getting the sum of the sales <=4days of each month.

their year start on february the 1st of each year and not on january that why i'm using min(month + 1)

swuehl
MVP
MVP

Hi layal,

ups, you got me. My expression will limit the evaluation to days of months < current day of months.

That's absolutely nonsense, of course.

I guess you have a date field, too. (if not, that could be created during load, see my example).

=sum({$<Year = {$(=year(today())-1)}, Month = {">=$(=min({<Year={$(=year(today())-1)}>} Month)+1) <=$(=max({<Year={$(=year(today()))}>} Month))"},Date={"<=$(=date(today()))"}>} Value)

should work, now.

Sorry for my mistake, I think I need some sleep,

Stefan