Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a YTD chart and a MTD chart that I need to compare the same time period from the previous year (YTD) and compare the MTD to the same month from the previous year.
We are on a fiscal year schedule July-June. I have a variable called vCurGLMonth that defines the current month (as opposed to the calendar month).
For the YTD number, I need to take the most recent number (NOT the SUM) of the YTD_GL_AMT.
For example, if we look at AU_NUM = 848350 and FISCAL_YEAR = FY17, we now have data from July-November. This YTD number should be $781,140. This is correct:
=Sum({<FISCAL_MONTH_YEAR={'$(=Max(FISCAL_MONTH_YEAR))'}>}YTD_GL_AMT)
QUESTION 1: How do I show the same time period from last year?
For the MTD number, it's a sum of MTD_GL_AMT.
Try:
=Sum({<FISCAL_YEAR=, FISCAL_MONTH=, FISCAL_MONTH_YEAR={"$(=Date(AddYears(Max(FISCAL_MONTH_YEAR),-1),'YYYY/MM'))"}>}YTD_GL_AMT)
Try:
=Sum({<FISCAL_YEAR=, FISCAL_MONTH=, FISCAL_MONTH_YEAR={"$(=Date(AddYears(Max(FISCAL_MONTH_YEAR),-1),'YYYY/MM'))"}>}YTD_GL_AMT)
Try this:
=Sum({<FISCAL_MONTH_YEAR={"$(=Date(AddYears(Max(FISCAL_MONTH_YEAR), -1), 'YYYY/MM'))"}, FISCAL_YEAR>} YTD_GL_AMT)
Thanks, One more follow up question...
The YTD Budget and Actual numbers are blank if all I have selected is a single FY. How can I fix this?
If I pick an AU, then they show up but I would expect the numbers to be there without making an AU selection.
Check out the attached
Thanks Sunny! Can you walk me through this statement?
FISCAL_MONTH_YEAR={"$(=Max({<YTD_BUDGET_AMT = {'*'}>}FISCAL_MONTH_YEAR))"}
Basically saying that give me the max FISCAL_MONTH_YEAR where I have YTD_BUDGET_AMT....
Try these two in a text box object without selection
1) =Max({<YTD_BUDGET_AMT = {'*'}>}FISCAL_MONTH_YEAR)
2) =Max(FISCAL_MONTH_YEAR)