topic Re: YTD Calc using SelectedYearVariable in QlikView

YTD Calc using SelectedYearVariable

Thu, 01 Aug 2013 02:41:46 GMT

Hi,

I need to calculate Net Income comparing Selected Fiscal YTD to Previous Fiscal YTD (ie. July 1-August 1, 2013 compared to July 1-August 1, 2012). I have a variable that drives which year is selected 'vBrokerageSelectedYear' which looks likes this:

vBrokerageSelectedYear=if(IsNull (Fiscal_Year),Year(addmonths(Today(),6)),Fiscal_Year)

I can't change the variable because it drives many other tabs.

This is the expression I'm using to try and calculate the previous year's Net_Income based on Fiscal_Year and Invoice_Period_Field:

=num(sum({<Fiscal_Year={"$(=vBrokerageSelectedYear-1)"},Fiscal_Year_Earned=,I_Month=,I_Month_Earned=, Invoice_Period_Field={"<=$(=Num(Date#(Day(today())&'-'&Month(Today())&'-'&(vBrokerageSelectedYear-1),'DD-MMM-YY'),0))"}>}Net_Income),'$#,##0')

The expression returns the FULL Previous Fiscal Year, not YTD. I believe the problem lies somewhere in the Invoice_Period_Field part of the expression.

Any assistance would be greatly appreciated.

Thank you in advance,

Matt

Re: YTD Calc using SelectedYearVariable

tresB — Thu, 01 Aug 2013 04:29:38 GMT

Can you share your app sample?

Re: YTD Calc using SelectedYearVariable

Thu, 01 Aug 2013 05:30:57 GMT

Hi tres qv,

I've attached the sample.

Thanks for taking a look.

Re: YTD Calc using SelectedYearVariable

tresB — Thu, 01 Aug 2013 06:02:38 GMT

the max date for the year 2013 available here in your app is 30/06/2013 (Invoice_Period_Field), hence the LYTD is full year data. i suggest you to check the same with some data for a date greater than today(last year). otherwise the behaviour seems OK to me.

Re: YTD Calc using SelectedYearVariable

Thu, 01 Aug 2013 06:12:15 GMT

Hi tres qv,

Thanks for the response. That's because our fiscal year runs from July 1st to June 30th. So the last day of Fiscal Year 2013 is 30/06/2013.

I'm not sure I understand what you mean when you say, "hence the LYTD is full year data."

What I'm trying to achieve is that if I were to run the refresh today, August 1st, 2013, I want to see data from "FiscalYear to date 2013" as July 1st, 2012 through August 1st, 2012. and I want to see data from "FiscalYear to date 2014" as July 1st 2013 though August 1st 2013.

I apologize if I wasn't clear before.

Thank you!

Re: YTD Calc using SelectedYearVariable

tresB — Thu, 01 Aug 2013 07:22:35 GMT

Try this:

=sum({<Fiscal_Year={"$(=vBrokerageSelectedYear-1)"},Fiscal_Year_Earned=,I_Month=,I_Month_Earned=,Invoice_Period_Field={"<=$(=(Addmonths(today(),-12)))"}>}Net_Income)

Re: YTD Calc using SelectedYearVariable

Thu, 01 Aug 2013 22:43:17 GMT

tres qv,

Thank you for the reply. That works for the current year, but as I go further back, say 2012 for example, the same problem exists. I need vBrokerageSelectedYear to be the driving force for both the Fiscal_Year half of the expression AND the Invoice_Period_Field of the expression. Any kind of 'today()' I'm afraid won't work.

Do you know how I can incorporate vBrokerageSelectedYear into the Invoice_Period_Field?

Thank you.

Re: YTD Calc using SelectedYearVariable

tresB — Fri, 02 Aug 2013 06:06:24 GMT

PFA

Hope this helps.

Note: If you peek year from today() while deriving last-year-today date in the set analysis, it might not always be same as fiscal year (unless untill converted accordingly) and i believe that was causing the issue in the earlier version of expression.