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: 
cbaqir
Specialist II
Specialist II

Prev YTD and MTD

 

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.

 

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Try:

=Sum({<FISCAL_YEAR=, FISCAL_MONTH=, FISCAL_MONTH_YEAR={"$(=Date(AddYears(Max(FISCAL_MONTH_YEAR),-1),'YYYY/MM'))"}>}YTD_GL_AMT)


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
Gysbert_Wassenaar

Try:

=Sum({<FISCAL_YEAR=, FISCAL_MONTH=, FISCAL_MONTH_YEAR={"$(=Date(AddYears(Max(FISCAL_MONTH_YEAR),-1),'YYYY/MM'))"}>}YTD_GL_AMT)


talk is cheap, supply exceeds demand
sunny_talwar

Try this:

=Sum({<FISCAL_MONTH_YEAR={"$(=Date(AddYears(Max(FISCAL_MONTH_YEAR), -1), 'YYYY/MM'))"}, FISCAL_YEAR>} YTD_GL_AMT)

cbaqir
Specialist II
Specialist II
Author

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?

1-20-2017 9-43-39 AM.jpg

If I pick an AU, then they show up but I would expect the numbers to be there without making an AU selection.

1-20-2017 9-42-16 AM.jpg

sunny_talwar

Check out the attached

cbaqir
Specialist II
Specialist II
Author

Thanks Sunny! Can you walk me through this statement?

FISCAL_MONTH_YEAR={"$(=Max({<YTD_BUDGET_AMT = {'*'}>}FISCAL_MONTH_YEAR))"}

sunny_talwar

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)