Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

YTD Calc using SelectedYearVariable

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

7 Replies
tresesco
MVP
MVP

Can you share your app sample?

Not applicable
Author

Hi tres qv,

I've attached the sample.

Thanks for taking a look.

tresesco
MVP
MVP

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.

Not applicable
Author

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!

tresesco
MVP
MVP

Try this:

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

Not applicable
Author

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.

tresesco
MVP
MVP

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.