topic Re: set analysis YTD calculation in QlikView

set analysis YTD calculation

Fri, 16 Jan 2015 06:29:12 GMT

Hi,

I am using this expression to calculate sum of revenue for previous year

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

but i want this expression to show same period revenue for previous year as showing in current year

for ex if in 2015 there is only one month January it will be compare to jan month of 2014

and if there are three months in 2015 it will compare to same three months of 2014

Re: set analysis YTD calculation

jagan — Fri, 16 Jan 2015 06:52:27 GMT

Hi,

Try like this if you have a date field

For YTD

=sum({$<Year=,Month=, DateFieldName={'>=$(=YearStart(Today()))<=$(=Today())'}>}Revenue)

For Previous Year YTD

=sum({$<Year=,Month=, DateFieldName={'>=$(=YearStart(Today(), -1))<=$(=AddYears(Today(), -1))'}>}Revenue)

Hope this helps you.

Regards,

Jagan.

Re: set analysis YTD calculation

kavita25 — Fri, 16 Jan 2015 07:00:37 GMT

For Current year

sum({<Year={$(=Only(Year))},Month= {"<=$(=max({<Year={$(=Only(Year))}>} Month))"}>}Revenue)

For Previous Year

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

If You are using Master Calendar you can create flag for the same.

For Current Year YTD

if(if(Month(date(Floor(Date)))<=3,(Year(date(Floor(Date)))-1)&'-'&(Year(date(Floor(Date)))),(Year(date(Floor(Date))))&'-'&(Year(date(Floor(Date)))+1))=if(Month(((today())))<=3,(Year(((today())))-1)&'-'&(Year(((today())))),(Year(((today()))))&'-'&(Year(((today())))+1)),1,0) as YTD_Flag,

For Prior Year YTD

inyeartodate(Date, $(vDateToday), -1,4)* -1 AS LastYTDFlag

Hope it helps you

Regards,

Kavita

Re: set analysis YTD calculation

Fri, 16 Jan 2015 07:14:05 GMT

Thanks Jagan

That was helpful but it is not working for 2014 or 2013 bcz of today() can i replace this with my transactiondate?

pls suggest

Re: set analysis YTD calculation

jagan — Fri, 16 Jan 2015 08:34:05 GMT

Hi,

This will work

=sum({$<Year=,Month=, DateFieldName={'>=$(=YearStart(Today(), -1))<=$(=AddYears(Today(), -1))'}>}Revenue)

because I am using =YearStart(Today(), -1) this will give Jan-1-2014 and =AddYears(Today(), -1) will give Jan-16-2014

I am not sure why it is not working for you. Are you selecting any other date fields other than Year=,Month=? If yes then you have to exclude the selections by using (Quarter=), etc.

Regards,

Jagan.

Re: set analysis YTD calculation

nikhilgarg — Fri, 16 Jan 2015 10:06:46 GMT

HEy,

You mean to say either go with the above given script or with Master calendar script. IS it so ?

Re: set analysis YTD calculation

kavita25 — Fri, 16 Jan 2015 10:15:46 GMT

Yess.....

Above given script can be used directly in the expression, which is little bit complex.

And the same can be achieved by creating flags and directly can be use in expression which is simpler than the writing whole expression.

Re: set analysis YTD calculation

nikhilgarg — Fri, 16 Jan 2015 10:18:38 GMT

Ok . Actually for me below script is easier than master calendar script.

For Current year

sum({<Year={$(=Only(Year))},Month= {"<=$(=max({<Year={$(=Only(Year))}>} Month))"}>}Revenue)

For Previous Year

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

Re: set analysis YTD calculation

kavita25 — Fri, 16 Jan 2015 10:38:38 GMT

Yeah....good for you.

Re: set analysis YTD calculation

Pdhondi — Sun, 31 Mar 2019 05:41:58 GMT

am using this script

Sum({<Year={$(=Only(Year))}>}Sales)

but the result is 0