#January0 discussing

I would like your help with the following:

* 3 calculated columns in one table.

* Total Xmas sales last year, Xmas sales up to this week last year, and Xmas sales this year

*Combining all the columns, I get a wrong last year total.

If I comment out this week last year, I get a correct last year total.. I think the problem is with my calculated field logic

IF  (DATEPART('year',[Enquiry Created]) = 2016 AND DATEPART('week',[Enquiry Created]) <= DATEPART('week',today(), 'sunday')+1) THEN '2016 Now'

ELSEIF  DATEPART('year',[Enquiry Created]) = 2017 AND [Event Date] >= ⌗November 1, 2017# AND [Event Date] <= #January 31, 2018# THEN '2017 Now'

ELSEIF  [Event Date] >= #October 1, 2016# AND [Event Date] <= #January 31, 2017# THEN '2016 Total'

ELSE 'NULL'

END

Thanks for your help

Hi Tableau community,

I have the base code for the case statement below, but I'm not sure what the best function is to define these ranges so that it will work as required when added to the filter shelf.

Parameter = [DateRange]

Dimension = [Date]

CASE [DateRange]

    WHEN '12 Months' THEN

               Need logic that uses [Date] to define range 12 months prior to the current date

    WHEN '6 Months' THEN

    WHEN '3 Months' THEN

    WHEN 'Year to Date' THEN

END

Thanks for any help!

Kevin

Hi,

I have a worksheet in my dashboard that has multiple values being displayed in rows with weeks as the column.  I have a parameter filter that allows the user to select their date range based on the below two calculated fields.  This all works great and I have no problem with them.  What's causing the issue is one of the measure values I now need to display on the row (Retention Rate below) is calculated with data from the previous week.  This calculation works correctly but because it's going back one week it's causing the first week in the date range to not show for this one measure.  If there a way to make my date range behave differently for this one measure or is there a better way to use the value from the previous week than what I have done?  Thanks in advance for your help.

Selected Start Date

CASE [Select Date Range]

WHEN "All" THEN DATETRUNC('month',[Full Date])

WHEN "3 months" THEN DATEADD('month',-3,TODAY())

WHEN "6 months" THEN DATEADD('month',-6,TODAY())

WHEN "1 year" THEN DATEADD('month',-12,TODAY())

WHEN "2011" THEN DATETRUNC('month',#January 1, 2011#)

WHEN "1H 2011" THEN DATETRUNC('month',#January 1, 2011#)

WHEN "2H 2011" THEN DATETRUNC('month',⌗July 1, 2011#)

WHEN "2012" THEN DATETRUNC('month',#January 1, 2012#)

WHEN "1H 2012" THEN DATETRUNC('month',#January 1, 2012#)

WHEN "2H 2012" THEN DATETRUNC('month',⌗July 1, 2012#)

END

Show Dates

DATETRUNC('week',[Full Date])>=DATETRUNC('week',[Selected Start Date])

Retention Rate

SUM([Retained Users])/WINDOW_SUM(SUM([Unique Users]),-1,-1)

I am trying to calculate a compliance rate for patient observations performed. by unit each month. We have 4 hospital units and the expectation is that one patient observation will be performed on each shift on each unit.

During a 31-day month, there should be 93 observations per Unit. During a 30-day month, it would be 90, etc.

I created a calculated field called 'Observation Compliance' with the goal of getting a compliance rate for the month, by unit; however, it is only returning the count of observations.

I tried using and LOD expression (maybe it's my order of operations), or else I'm just overengineering the calculation altogether.

Here is the expression with the packaged workbook attached:

If DATE([Date of Observation])>=⌗November 1, 2023# and DATE([Date of Observation])<=⌗November 30, 2023# THEN {FIXED [Unit]: COUNT([Continuous Observation Data])/90}

ELSEIF DATE([Date of Observation])>=⌗December 1, 2023# and DATE([Date of Observation])<=⌗December 31, 2023# THEN {FIXED [Unit]: COUNT([Continuous Observation Data])/93}

ELSEIF DATE([Date of Observation])>=#January 1, 2024# and DATE([Date of Observation])<=#January 31, 2024# THEN {FIXED [Unit]: COUNT([Continuous Observation Data])/93}

ELSEIF DATE([Date of Observation])>=⌗February 1, 2024# and DATE([Date of Observation])<=⌗February 29, 2024# THEN {FIXED [Unit]: COUNT([Continuous Observation Data])/87}

END

Any help would be greatly appreciated.

Thank you,

Jason

Hello @Jim Dehner​ ,

I am trying to find the number of Continuing parking permits in my report - the permits issued from September 1st of the current fiscal year. We follow the Fiscal year from Sept 1st to Aug 31st.

I created the following formulas to create the continuing field to calculate these records, please let me know if this is correct. I see a slight difference in the count when I validate my results, so I am trying to understand where I am going wrong in the calculations.

1. Created Date -FY field (by setting the default properties to Fiscal year "Sept")

[Issue date]

2. Created Date-FY Start date field

IF (MONTH(TODAY()-1) >= MONTH(#September 1, 2021#)

AND 

MONTH(TODAY()-1) <= MONTH(⌗December 31, 2021#))

THEN DATE ("09/01/"+DATENAME('year', TODAY()-1))

ELSEIF

(MONTH(TODAY()-1) >=MONTH(#January 1, 2022#)

AND 

MONTH(TODAY()-1) <= MONTH(⌗August 31, 2022#))

THEN DATE ("09/01/"+DATENAME('year', DATEADD('month',-12,TODAY()-1)))

END

3. Created the continuing permits field

IF [Date-FY]< [Date-FY start date] THEN 0

ELSEIF [Date-FY]> (TODAY()-1)THEN 0

ELSE [TotalCount]

END

Attached is the workbook, please let me know if you are able to access and view the data with calculations.

Any help would be appreciated. Thanks!