topic YTD Help with Fiscal Year in QlikView

YTD Help with Fiscal Year

Mon, 12 Sep 2011 18:27:48 GMT

Hi, i've made a graph with the following expression to calculate YTD

sum({$<Year = {$(=max(Year))}, Month = {"<=$(=max({<Year={$(=max(Year))}>} Month))"}>} Amount)

It works great and now I need to make something similar but YTD must be between June and July, for ex: 06/2010 - 07/2011. I searched the community but i didn't find the right answer to my problem

anybody can help me?

Thanks in advance

Re: YTD Help with Fiscal Year

Mon, 12 Sep 2011 18:56:12 GMT

HI Mario,

see if this is ok for u.

I give u 2 solutions.

C u,

Stefano

YTD Help with Fiscal Year

Mon, 12 Sep 2011 19:25:41 GMT

Stefano, this is not exactly what i need, taking the values from your example would be:

if i select:

month = 5 YTD must be = 40

month = 6 YTD must be = 96 (40+56)

month = 7 YTD must be = 70 (this is because that my fiscal year starts at July and ends at June of the next year, then at July of each year begins sum from 0)

Thanks!

YTD Help with Fiscal Year

Mon, 12 Sep 2011 19:46:31 GMT

My suggestion would be to add fiscal data to your date dimension. That way you can reference either the calendar period or the fiscal period.

When I build out this type of data I have two period options: CalendarPeriod and FiscalPeriod. You can then customize your report to use either (or both). Users can select CalendarQ1 or FiscalQ1 as required.

Modifying your date data will be a lot easier, and more maintainable thanks to leap years, than trying to code something in QV to handle the offset.

For what it's worth, if you decide to work with your date dimension consider adding offsets to make comparisons easier. For example, adding something like a CalendarMonthOffset column will allow reports to do things like compare current calendar month with previous month (offset = 1), MTD vs prior year MTD (offset = 12), etc.

Hope this helps.

Re: YTD Help with Fiscal Year

Mon, 12 Sep 2011 21:33:21 GMT

"Maybe" this is near to the solution.

Stefano.

YTD Help with Fiscal Year

Mon, 12 Sep 2011 21:42:30 GMT

Stefano, yes it's near but when i select for ex. year=2011 month=8 on the graph the result i've got is 12, and it has to be 34 (11 + 23)

Thanks a lot

Re: YTD Help with Fiscal Year

Mon, 12 Sep 2011 21:53:40 GMT

Mario, are u italian?

34 is the solution if u change '-' with '+' in the expression.

I've underestood u had a ytd in the table, i u have MTD change that and it should be right...

Otherwise I surrender and go to bed..

YTD Help with Fiscal Year

Mon, 12 Sep 2011 22:09:30 GMT

Stefano, it's close but if for example you select Month = 9 obtained 79 (23 +56), when it should be 90 (11 +23 +56) in the formula brings only two months...

Cmq mille grazie e buona notte!

PS: i'm from Argentina but muy parents are italians

Re: YTD Help with Fiscal Year

Mon, 12 Sep 2011 22:13:12 GMT

This is perfect!

...and the winner is....

after th time i "loose" it must be right!

Now i have really to go to bed... i have to develope with this Qlik tomorrow at work...

C u

Stefano.

YTD Help with Fiscal Year

Mon, 12 Sep 2011 22:22:36 GMT

EXCELLENT!! FANTASTICO!!

WORKS GREAT!!

Thank u very much !!