topic Fiscal Calendar YTD Query - Please Help in QlikView

Fiscal Calendar YTD Query - Please Help

Mon, 17 Jun 2013 13:46:22 GMT

Hi Qlik Community,

I am new to this and this is my first post. It would be very helpful if you could please explain the following.

I am trying to use a Fiscal year calendar where the year starts in December and I have used the following for Current YTD and Prior YTD.

inyeartodate(TempDate, $(varToday), 0, 12) * -1 AS CurYTDFlag,

inyeartodate(TempDate, $(varToday), -1, 12) * -1 AS LastYTDFlag

The only problem is that as of today my data is only for YTD end of May. When it calculates the Prior YTD it counts data all the way to today's date last year. How do i make the above script to stop at end of previous month - which in this case is May 2013. I just want the above to calculate the YTD from Dec 2012 to May 2013 and Dec 2011 to may 2012.

Thanks,

Harsha

Re: Fiscal Calendar YTD Query

marcus_sommer — Mon, 17 Jun 2013 14:00:00 GMT

see for example here:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/28/fiscal-year

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/10/relative-calendar-fields

- Marcus

Re: Fiscal Calendar YTD Query

Mon, 17 Jun 2013 14:55:12 GMT

Hi Marcus,

Thanks for your response through the 2 links. I read these two but the solutions provided don't really seem to help.

Regards,

Harsha

Re: Fiscal Calendar YTD Query

marcus_sommer — Mon, 17 Jun 2013 15:58:08 GMT

I think you must reduce the second parameter (variable) for the last year, therewith both time-areas are equal. Try this:

inyeartodate(TempDate, $(varToday) - ($(varToday) - makedate(year($(varToday), 12, 1)) , -1, 12) * -1 AS LastYTDFlag

- Marcus

Re: Fiscal Calendar YTD Query

Mon, 17 Jun 2013 17:47:36 GMT

Sorry Marcus,

Have been working on this - Tried everything but this does not seem to work.

Harsha

Re: Fiscal Calendar YTD Query

marcus_sommer — Tue, 18 Jun 2013 08:28:02 GMT

Yes my suggestions was to fast and wrong - I think the inyeartodate()-function could not work in this case, because inyeartodate will always return a complete year if data appropriate available.

Try this as approach:

if(TempDate >= makedate(year(setdateyear($(varToday), year($(varToday)) - 1), 12, 1) and

TempDate < setdateyear($(varToday), year($(varToday)) - 1), true(), false()) as LastYTDFlag

- Marcus

Re: Fiscal Calendar YTD Query

Tue, 18 Jun 2013 08:36:39 GMT

for YTD TRY THIS CODE

SUM({<DATE={">=$(=yearStart(max(DATE))) <= $(=max(DATE))"},year=,Month=>}Sales)

Re: Fiscal Calendar YTD Query

Tue, 18 Jun 2013 09:09:08 GMT

Hi Marcus,

Sorry for the trouble. Tried the approach but this still does not work.

Regards,

Harsha

Re: Fiscal Calendar YTD Query

Tue, 18 Jun 2013 09:10:09 GMT

Hi Vishwa,

Thanks for your help with this. I am looking for something that fits into the Load script rather than an expression.

Regards,

Harsha

Re: Fiscal Calendar YTD Query - Please Help

Wed, 19 Jun 2013 11:16:42 GMT

Hello Qlik Community,

Can someone pelase help me with this? It would be much appreciated.

Thanks,

Harsha

Re: Fiscal Calendar YTD Query - Please Help

marcus_sommer — Wed, 19 Jun 2013 11:35:16 GMT

What don't work, which results do you received? Possibly with a little changes it will work. Perhaps it is better you gives us a small example app.

- Marcus