6 Replies Latest reply: Aug 15, 2016 9:27 AM by Jaz Singh RSS

    create flag to use for comparison with expression counts of reported incidents

    Jaz Singh

      Hi everyone.

       

      I am trying to create a chart where I can compare number of incidents reported this year (up to todays date, day number) vs previous years (also accordingly up to same day number from 2007 and upwards).

       

      Example:

       

      Todays day number of year is 222, then compare;

       

      2007 - up to day 222 incidents reported

      2008 - up to day 222 incidents reported

      .......

      2016 - up to day 222 incidents reported.

       

       

      In my script I have the following:

       

       

          LOAD
           
      TempDate AS %IncidentReportedKey,
           
      TempDate AS  [Incident ReportedDate],
           
      Week(TempDate) AS ReportedWeek,
           
      Year(TempDate) AS ReportedYear,
           
      Month(TempDate) AS ReportedMonth,
           
      Day(TempDate) AS ReportedDay,

       

      I then go on to create a flag, to check whether if day number of year is less than todays date.

       

              if(Day(TempDate) <= Day(today()), 1, 0) as IncidentReportedDate_Flag.

       

      I then use this flag in an Expression in a chart, like this, with a variable vUpToToday = 1;

       

          = if(vUpToToday = 1, =count({$<ReportedYear={">2007"}>},IncidentReportedDate_Flag={1}>}DISTINCT(Incidents))

            ,

              =count({$<ReportedYear={">2007"}>}DISTINCT(Incidents)) )

       

      I have created a button that when hit acitvates the variable vUpToToday = 1, and then shows the respected comparisons, else just shows reported incidents from year to year.

       

       

      I think my problem is with the written flag, how can I manage to create a flag that focuses on days in year?