YTD Help with Fiscal Year

[#1]

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

[#2]

HI Mario,

see if this is ok for u.

I give u 2 solutions.

C u,

Stefano

[#3]

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!

[#4]

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.

[#5]

"Maybe" this is near to the solution.

Stefano.

[#6]

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

[#7]

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..

[#8]

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

[#9 · Accepted solution]

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.

[#10]

EXCELLENT!! FANTASTICO!!

WORKS GREAT!!

Thank u very much !!