3 Replies Latest reply: Dec 22, 2016 9:39 PM by Sunny Talwar RSS

    Date Questions

    Cassandra Baqir

       

      Hi everyone. I am hoping for some help on the chart with the requirements in red below. I've attached a sample.

       

      Requirements:
      Chart 1:
      X-Axis - Show weeks in current month, week should reflect a Thursday date

      x-axis - Show SERVICE_AREA_SHORT to show trending

      y-axis - Count of distinct DEFECT_ID where GPQ2_NAME is ERC - Routing by Wednesday at noon PST (GPQ2_START_DATE & GPQ2_START_TIME)

      y-axis - show stacked bar chart where CARE_SETTING = 'Acute' or 'Ambulatory'

      Any help is appreciated! Thanks!

        • Re: Date Questions
          Sunny Talwar

          Numerically what are you expecting to see?

            • Re: Date Questions
              Cassandra Baqir

              Not sure I know yet. It's more of how do I only show the weeks in the current month and how do I account for the requirement for the status of by Wednesday at noon? Here is what I have:

               

              Expression 1 (Acute): =count({<CARE_SETTING={'Acute'},GPQ2_NAME={'ERC - Routing'},TRX_DATE={">=$(=addmonths(monthstart(today()),-1))<=$(=addmonths(monthend(today()),0))"}>}distinct DEFECT_ID)

              Exp 1 Issue - not only showing weeks from current month

               

              Dimension: =if(weekday(date(TRX_DATE)) = ('Sun' or 'Mon' or 'Tues'),date(WEEK_START),
              if(weekday(date(TRX_DATE)) = ('Wed') and GPQ2_START_TIME < '12 PM', date(WEEK_START),
              date(WEEK_START+7)))

               

              Issue: If I look at DEFECT_ID = 7632, the GPQ2_START_DATE is a Wed (12/7) and the time is before noon so it should be counted witht eh current week but it is showing as the week after (12/11).

                • Re: Date Questions
                  Sunny Talwar

                  Is this what you want?

                   

                  Capture.PNG

                   

                  When I opened your app and checked DEFECT_ID = 7632, it was showing on 12/01... are you seeing 12/11?

                   

                  Capture.PNG

                   

                  Expression for Acute

                  =Count(DISTINCT{<CARE_SETTING={'Acute'}, GPQ2_NAME={'ERC - Routing'}, TRX_DATE={"$(='>=' & Date(MonthStart(Today()), 'YYYY-MM-DD') & '<=' & Date(MonthEnd(Today()), 'YYYY-MM-DD'))"}>} DEFECT_ID)

                   

                  Expression for Ambulatory

                  =Count(DISTINCT{<CARE_SETTING={'Ambulatory'},GPQ2_NAME={'ERC - Routing'}, TRX_DATE={"$(='>=' & Date(MonthStart(Today()), 'YYYY-MM-DD') & '<=' & Date(MonthEnd(Today()), 'YYYY-MM-DD'))"}>} DEFECT_ID)

                   

                  Dimensions

                  SERVICE_AREA_SHORT

                  =WeekStart(GPQ2_START_TIME +0.5, 0, 3)