9 Replies Latest reply on Jan 15, 2019 2:31 PM by Fernando Guerrero

Line graph question: How do I get current year to stop at the current month when comparing it to previous years?

Hello all, I'm in need of help with a problem I've had with line charts for a while now. When comparing the current year to previous years using a running sum everything shows fine but on the current year when it reaches months that have not yet occurred the line just goes flat. Does any one know how to fix it to where the line just ends at the current month instead of dragging on to the end of the chart?

• 1. Re: Line graph question: How do I get current year to stop at the current month when comparing it to previous years?

if datetrunc('month',[Order Date])<=datetrunc('month',today()) then [Sales] end

jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• 2. Re: Line graph question: How do I get current year to stop at the current month when comparing it to previous years?

Are you doing a running_sum on those lines?

If so, put the conditional check that Jim suggested in that running sum calc.  (And if you are using a quick table calc, then write your own calc instead, so you have control over the values.

if datetrunc('month',[Order Date])<=datetrunc('month',today()) then  RUNNING_SUM([CNTD([Employee ID])) END

• 3. Re: Line graph question: How do I get current year to stop at the current month when comparing it to previous years?

Thanks Jim & Joe for the quick reply. Unfortunately this did not work it just nulled everything out. Below is the formula I used, had to wrap hire date in ATTR to make formula work.

if datetrunc('month',ATTR([Hire Date]))

<=datetrunc('month',today())

then  RUNNING_SUM(COUNTD([Employee ID])) END

• 4. Re: Line graph question: How do I get current year to stop at the current month when comparing it to previous years?

Upload a sample workbook.  Maybe the IF logic needs to be inside the Running sum rather than the running sum being inside the IF logic.  I can mess with it.

• 5. Re: Line graph question: How do I get current year to stop at the current month when comparing it to previous years?

Thank you. Attached is the packaged workbook.

• 6. Re: Line graph question: How do I get current year to stop at the current month when comparing it to previous years?

Does your fiscal year start in September?

• 7. Re: Line graph question: How do I get current year to stop at the current month when comparing it to previous years?

Yes it does

• 8. Re: Line graph question: How do I get current year to stop at the current month when comparing it to previous years?

See attached.

I had to do some extra stuff to get around the way your data is set up.  If you had actual years in your dates, I could have worked off those, but instead I used the [New Hires] field to control what year to chop off.

So first I made a fiscal month field.  You get 1-through-12 from September through August.

And I made a Fiscal Month today calc to tell me what today's fiscal month is.

From there, I did logic in a calc called Display Distinct Count.  If I'm in the last year, then figure out where to stop the running sum.  Otherwise, do the running sum for the whole year.

• 9. Re: Line graph question: How do I get current year to stop at the current month when comparing it to previous years?

Thank you very much for the help