Fiscal Calendar YTD Query - Please Help

[#1]

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

[marcus_sommer]

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

[#3]

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

[marcus_sommer]

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

[#5]

Sorry Marcus,

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

Harsha

[marcus_sommer]

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

[#7]

for YTD TRY THIS CODE

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

[#8]

Hi Marcus,

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

Regards,

Harsha

[#9]

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

[#10]

Hello Qlik Community,

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

Thanks,

Harsha