Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jerome_hanff
Not applicable

YTD

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

Tags (1)
8 Replies
Miguel_Angel_Baeyens
Not applicable

YTD

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

jerome_hanff
Not applicable

Re: YTD

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

ytd.jpg

Miguel_Angel_Baeyens
Not applicable

Re: YTD

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

jerome_hanff
Not applicable

Re: YTD

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

can you help me?

Miguel_Angel_Baeyens
Not applicable

Re: YTD

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

jerome_hanff
Not applicable

Re: YTD

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)

Miguel_Angel_Baeyens
Not applicable

Re: YTD

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

jerome_hanff
Not applicable

Re: YTD

GREAT!!! it's working!!!

thany very very much!