14 Replies Latest reply: Jul 27, 2015 1:30 PM by Sunny Talwar RSS

    Using MonthEnd in If Statement

    Cassandra Baqir

      I have a graph (attached) that shows Actuals in a solid line and then turns into a dotted line for Forecasted hours.

       

      The actuals get posted on the last day of the month so since it is currently July, I would expect to see the solid line end in June. Instead, it's showing through the end of this month. I tried to overcome this by using:

       

      Dimension: =date(FISCAL_MONTH_YEAR, 'YYYY-MMM')

      Expression:  if(FISCAL_MONTH_YEAR>Monthend(today())+1,sum(FCST_AMT),sum(ACT_AMT))

       

      Line Style: if([FISCAL_MONTH_YEAR]>today(),'<S3>','<S1>')

       

      Even though the dotted line is starting in August now, my July date has disappeared. which shouldn't be the case.

       

      How do I get the current month to display but the dotted line starting from the last month?

       

      Thanks,

      Cassandra