Date Questions

[cbaqir]

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!

[sunny_talwar]

Numerically what are you expecting to see?

[cbaqir]

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).

[sunny_talwar]

Is this what you want?

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

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)