YTD For Fiscal Year

[Anonymous]

Hi Guys,

Can you please help me to resolve an issue. So, the problem is the following: I have 4 calendar years(2006,2007,2008,2009) with 3 years of fiscal data(2007,2008,209). My fiscal year of 2007 starts in 1 Jul 2006 and ends in 30 Jun 2007 and so on for the other years. I want to create a bar chart that shows me the sum of the sales from the beginning of the fiscal year to the current selection of the fiscal year. For Example: If I choose July, I only want to see the bar for July, If I choose October, I want to see bars for Jul, Sep, Oct, Nov. I have written some set analysis code that works good only If I choose month till Dec. When I choose Jan I want to see bars from Jul 2006 to Jan 2007 but instead I only see the bar for Jan 2007. There is my code:

sum({<Year={">$(=YearStart(FiscalYear))<= $(=Max(Year))"},Month={">$(MonthStart(FiscalMonth))<= $(=max(Month))"}>}Sales)

P.S Month For July = 7, FiscalMonth for July = 1 and so on till Month for June = 6, FiscalMonth for Jun = 12

Thank you in advance!

[sunny_talwar]

Do you have a MonthYear field in your dashboard?

[Anonymous]

Yes, I have a calculated dimension which is concatenation of Month&Year both not fiscal

[sunny_talwar]

This is created in the script? If not, can you create a new MonthYear field in the script to do this?

[Anonymous]

Yes, This is already created in the script!

[sunny_talwar]

What is the exact script you use to create the MonthYear field?