topic Re: YTD For Fiscal Year in QlikView

YTD For Fiscal Year

Anonymous — Fri, 20 Jul 2018 08:38:10 GMT

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!

Re: YTD For Fiscal Year

sunny_talwar — Fri, 20 Jul 2018 15:20:25 GMT

Do you have a MonthYear field in your dashboard?

Re: YTD For Fiscal Year

Anonymous — Fri, 20 Jul 2018 16:53:31 GMT

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

Re: YTD For Fiscal Year

sunny_talwar — Fri, 20 Jul 2018 18:22:28 GMT

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

Re: YTD For Fiscal Year

Anonymous — Mon, 23 Jul 2018 07:36:59 GMT

Yes, This is already created in the script!

Re: YTD For Fiscal Year

sunny_talwar — Mon, 23 Jul 2018 11:07:24 GMT

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