1 Reply Latest reply on Sep 14, 2018 5:06 PM by Shinichiro Murakami

    Line graph by year, display bull data as zero but hide future months

    Jordan Ross

      Ok, so we have several line graphs (Epi Curves) that display either counts or rates by month from 2015 and later. Each year is its own line.

       

      The dashboard uses line-list data and there are some months that have no events for a particular indicator and thus no data is present.

       

      We want past Dates before the MAX([EVENT_DATE]) to show as zero, and dates after it to not be shown at all.

       

      I've played around with it a bunch but have so far been unsuccessful. Below are 4 examples of how it is displayed in various combinations.

       

       

      1) If I have MONTH([EVENT_DATE]) as continuous, and use "ZN(IIF(ISNULL(lookup(COUNTD([SEQNUM]),0)),0,(COUNTD([SEQNUM])),NULL))" as the count, nulls between the earliest and latest record for each year will show as zero, but if the first month (or more) are also zero, they just aren't shown. However, no dates beyond the max date are shown either.

      Epi 1 - Continuous ZN.PNG

       

      2) If I have MONTH([EVENT_DATE]) as discrete, and use "ZN(IIF(ISNULL(lookup(COUNTD([SEQNUM]),0)),0,(COUNTD([SEQNUM])),NULL))" as the count, all nulls including "future" dates are shown as zero.

      Epi 1 - Discrete ZN.PNG

       

      3) If I have MONTH([EVENT_DATE]) as continuous, and use "COUNTD([SEQNUM])" then doesn't show data before or after first/last data point, and skips over nulls in between.

      Epi 1 - Continuous.PNG

       

      4) If I have MONTH([EVENT_DATE]) as discrete, and use "COUNTD([SEQNUM])" then only shows data where it exists.

      Epi 1 - Discrete.PNG