topic YTD till current day in QlikView

YTD till current day

Mon, 04 Jul 2011 12:21:51 GMT

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:)

YTD till current day

swuehl — Mon, 04 Jul 2011 13:08:36 GMT

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

YTD till current day

Mon, 04 Jul 2011 13:27:50 GMT

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??!

YTD till current day

swuehl — Mon, 04 Jul 2011 13:41:56 GMT

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

YTD till current day

Mon, 04 Jul 2011 13:50:49 GMT

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

YTD till current day

Mon, 04 Jul 2011 13:56:29 GMT

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

Re: YTD till current day

swuehl — Mon, 04 Jul 2011 14:49:04 GMT

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

Re: YTD till current day

swuehl — Mon, 04 Jul 2011 15:59:29 GMT

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

YTD till current day

Mon, 04 Jul 2011 16:37:57 GMT

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)

Re: YTD till current day

swuehl — Mon, 04 Jul 2011 17:23:47 GMT

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

Re: YTD till current day

swuehl — Mon, 04 Jul 2011 17:30:27 GMT

Hi layal,

another comment:

If customer has no data for january of a certain year,data starting in Feb, min(month + 1) would result in March, wouldn't it?

And talking about January, this month will never be taken into account then? (Because you start summing up the values not before February and stop with current month, maximum Dezember)

So in month January 2011, does this count to previous year 2010, then?

[I am not sure if I make myself clear, I think I go home now]

Regards,

Stefan

YTD till current day

Mon, 04 Jul 2011 17:39:12 GMT

hello stefan,

the customer always has data in january.in example if today is 04/07/2011 then YTD begins on 01/02/2011 till 04/07/2011 and LYTD begins on 01/02/2010 till 04/07/2010.

january is going to be the last month of the year not december.

YTD till current day

Mon, 04 Jul 2011 18:08:16 GMT

thanks for the help Stefan

as u said it works using the date...

best regards,

layal

Re: YTD till current day

swuehl — Tue, 05 Jul 2011 13:33:39 GMT

Hi layal,

I am glad to help you.

Being a little refreshed this morning, I think my expression is not absolutely correct:

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

will calculate the YTD last year, but not till today, but until end of month. In my example, you get 700 for the YTD last year column, but correct is 600 (disregarding the value of 15.7.2010).

I think correct ist:

=sum({$<Year = {$(=year(today())-1)},

Month = {">= 2"}, //$(=min({<Year={$(=year(today())-1)}>} Month)+1)"},

Date={"<=$(=date(makedate(year(today())-1,month(today()),day(today()))))"},

Day = >}

Value)

{I cleared the Day selection and limited Date to dates < same Date last year, not < today, you see the difference?}

But on another side, I still don't think that our calculation in case of january is correct (in case of a January date as today), It will not be taken as last month of a financial year correctly.

So I worked out a much better solution (in my opinion) for YTD last year:

=sum({1} if (InYearToDate (Date, today(), -1 , 2), Value))

This uses InYearToDate to determine if a given Date is to be taken into account (third parameter -1 for last year, fourth parameter 2 for financial year starts in February). I used set expression {1] to get all values, disregarding current selection, but you can easily change this.

See also attached example (where you can also set a date as reporting date in input field to play around or select the max. Date).

Hope my thought were correct and this is of any help, I learned a lot working on your problem 😉

Cheers,

Stefan