topic Re: YTD in QlikView

YTD

Anonymous — Wed, 19 Oct 2011 19:16:17 GMT

Hello

i want to calculate YTD and YTD -1 .

i have this right now

sum({$<YEAR = {$(=max(YEAR))}, MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}>} VALUESALES)

sum({$<YEAR = {$(=max(YEAR - 1))}, MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}>} VALUESALES)

But what i want is to go do the exact day in the month, not just the month

ex: from (YTD-1) 01.01.2009 to 19.10.2009 and 01.01.2010 to 19.10.2010

any idea how to complete this function to do so?

Thanx in advance for your help

YTD

Miguel_Angel_Baeyens — Wed, 19 Oct 2011 20:47:10 GMT

Hi Jerome,

There is a very similar issue solved few hours ago in this thread.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: YTD

Anonymous — Thu, 20 Oct 2011 15:33:10 GMT

=Sum({$<ANNEE={$(=Max(ANNEE)-1)}, BDLE_D_DATE={'<=$(=AddYears(vDate,-1))'}>} SALES)

I have this for 2010 (Year -1 ) but how can i have the same for 2009 2008 and 2007 ?

Re: YTD

Miguel_Angel_Baeyens — Thu, 20 Oct 2011 17:32:40 GMT

Hi,

I'd rather use the flags in the calendar table so you can show as many years comparing to the current as you want. You can even create a flag that will return true for all dates of al years from January 1 to today for 2009, 2010, 2011...

Check the attached example.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: YTD

Anonymous — Thu, 20 Oct 2011 18:20:31 GMT

I would rather prefer to do it directly in the definition field of the chart

can you help me?

Re: YTD

Miguel_Angel_Baeyens — Thu, 20 Oct 2011 19:48:24 GMT

Hi Jerome,

I'm afraid I don't understand quite well what you are up to. Would you please elaborate or upload some sample data so we can see what do you want to do and how to get the best solutions?

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: YTD

Anonymous — Thu, 20 Oct 2011 19:59:18 GMT

i want an expression that is valable for all the years in the chart, not only for year -1 (see image)

in BLUE ist the year sales from 01.01.YYYY to 31.12.YYYY

i want a second bar in the chart for each year with the sales from 01.01.YYYY to TODAY (pex 20.10.YYYY)

Re: YTD

Miguel_Angel_Baeyens — Thu, 20 Oct 2011 20:32:35 GMT

Hi Jerome,

Then use one dimension (Year) and two expressions, one without filter that will return the aggregated value of SALES per year

SUM(SALES)

And the other expression using the Date field compared to the result of the YearToDate function (that will return true/-1 if the date is between 01/01 and today)

SUM({< BDLE_D_DATE = {"=YearToDate(BDLE_D_DATE, Year(BDLE_D_DATE) - Year(Today())) = -1"} >} SALES)

However, this expression may not work because the YearToDate() function as everyting in the set analysis is evaluated once and for the whole chart, before rendering it, and not row by row. In this second case, you will need to use a If() condition that will slower your chart, and make cumbersome to write and debug your expressions, or even to create a caculated dimension to get only those dates that match the YTD.

Is far easier to maintain and to code the field in the Calendar table and use that flag later on in your tables, hence my suggestion in the attached applications.

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: YTD

Anonymous — Fri, 21 Oct 2011 09:31:10 GMT

GREAT!!! it's working!!!

thany very very much!