topic YTD using YEARSTART not working in QlikView

YTD using YEARSTART not working

Fri, 23 Jul 2010 12:59:49 GMT

Hello,

Please can someone tell me what is wrong with my YTD formula?:

SUM({$<Type = {'Wine'}, Date = {">=$(=YEARSTART(MIN(Date)))<=$(=MAX(Date))"} >} Sales )

Whenever I choose a week or month the YEARSTART is ignored and I get the number for sales during the selected period, not YTD.

I know =YEARSTART(MIN(Date)) works because I've tried it in a text box and it always returns 1st Jan of the selected/related year. But for some reason when I put it in a set, it doesn't.

Does anyone have any ideas?

Many thanks.

APS

YTD using YEARSTART not working

jonathandienst — Fri, 23 Jul 2010 20:16:57 GMT

I have two suggestions:

You are using a $ set. This means that the month selection is applied and then the set expression. Hence current month only. Change the first $ sign to 1 to apply the set expression to the unfiltered data.

Perhaps the problem is that the Max function returns a number. You may need to convert it to a date:

<=$(=MAX(Date)) .. change to ... <=$(=Date(MAX(Date)))

Hope these help

Jonathan

YTD using YEARSTART not working

Oleg_Troyansky — Sat, 24 Jul 2010 16:55:17 GMT

Jonathan's suggestion is a good one, but it might take you too far... using Set Identifier {1} instead of {$} causes ignoring all user selections. Perhaps you still need to respect their selections of other fields - Products, Regions, etc...

What you need to ensure is that any selection of Date-related fields (Year, Month, Quarter, Week) - are ignored. Add the following to your list of Set Modifiers:

Year=, Month=, Week=,

and all the selections of date-related fields will be ignored (if your fields are, indeed, named like these).

As a side comment - another, perhaps easier, way of validating YTD is using the function InYearToDate().

YTD using YEARSTART not working

Mon, 26 Jul 2010 10:21:47 GMT

Thanks for the response. I've used the method of ignoring the other date dimensions before (as you suggest) and it works although it got very complicated when I tried doing a year offset e.g current YTD vs. previous YTD.

I've not used INYEARTODATE before, perhaps this will be the best solution. Can you assist me in creating the expression?

YTD using YEARSTART not working

Oleg_Troyansky — Mon, 26 Jul 2010 22:26:01 GMT

It's quite difficult to get it right "on dry", without the actual data... If you could post a sample app, I would try and build the expression for you. Otherwise, please refer to the Help Section.

thanks!

YTD using YEARSTART not working

Tue, 27 Jul 2010 09:46:36 GMT

Hi Oleg, thanks for your help.

I will try to understand the InYearToDate and Year2Date functions as this will hopefully provide a "simple" solution to my problem.

Many thanks.

APS

YTD using YEARSTART not working

Tue, 27 Jul 2010 10:43:56 GMT

Should anybody be interested, here's one solution using my original expression to create YTD calculation:

SUM({$<Type = {'Wine'}, Date = {">=$(=YEARSTART(MIN(Date)))<=$(=MAX(Date))"} >} Sales )

And YTD-1:

SUM({$<Type = {'Wine'}, Date = {">=$(=YEARSTART(MIN(ADDYEARS(Date,-1))))<=$(=MAX(ADDYEARS(Date,-1)))"} >} Sales )

In both cases it is necessary to ignore all other date/time selections e.g. month, week, quarter etc (as Oleg states). This should be done in the set.

Hopefuly someone will find this useful.