Time Period Parameter

Question & Answer
, ,
1

I’m trying to have time period control (daily, weekly, monthly, Quarterly) and date range controls (start date) and (end date)

I have created 3 parameters as below;
1- (Name
TimePeriod
Data type (Not alterable after creation)
String
Values (Not alterable after creation)
Single value
Static default value
Daily)

2- (Name
StartDate
Data type (Not alterable after creation)
Datetime
Time granularity
Default date
Will always be 2023/01/01
Fixed date
2023/01/01)

3- (Name
EndDate
Data type (Not alterable after creation)
Datetime
Time granularity
Default date
Rolling: Today, 2024/05/19
Fixed date
Relative date
Today)

and 4 calculated fields for the periods as below;
Reporting Week: truncDate(‘WK’,date)
Reporting Month: truncDate(‘MM’,date)
Reporting Quarter: truncDate(‘Q’,date)
Reporting Date:
ifelse
(
${TimePeriod} = ‘Daily’, date,
${TimePeriod} = ‘Weekly’, {Reporting Week},
${TimePeriod} = ‘Monthly’, {Reporting Month},
${TimePeriod} = ‘Quarterly’, {Reporting Quarter},
null
)

I have defined specific values for the time period control (drop down menu)
Daily
Monthly
Quarterly
Weekly

And for the date range filter:

“Reporting Date
Between - StartDate and EndDate”

Filter type
TimeRangeFilter

Condition
BETWEEN

Use parameters

Start date parameter - start date
Include start date

End date parameter - end date
Include end date

My Difficulty is when I select Monthly time period and the start date not the first of the month the whole month will be ignored and show no data. I have to select 1st day of the month as start date to get the selected month’s data.

image
image1129×606 32.4 KB

In above snap Jan’s data didn’t show and Feb’s data shown for whole Feb!

What I’m trying to achieve here is to to show data on monthly view based on exact starting date to month and exact end date.

1 Like
2

Hello @abdulhzm, welcome to the QuickSight community!

Something to remember, when you convert a date using truncDate to a month date, then any date values present within that month will be returned. You will need to check your date values in comparison to your start and end date parameters before converting it to a month date value to filter out unselected dates.

ifelse(${StartDate} <= {Date} AND ${EndDate} >= {Date} AND ${TimePeriod} = 'Monthly', {Reporting Month}, NULL)

This above example only contains the logic for managing the month dates, but this should help you resolve the issue you are facing. I’ll mark this response as the solution, but please let me know if you have any remaining questions. Thank you!

3

Hi @DylanM, apologize for the late response. I have tried the provided solution however it didn’t solve the issue and gives same results attached above (ignores start date if it’s not the 1st of a month).

1 Like
4

Hello @abdulhzm, yes, I see. Okay, so we need to add one more check in the above calculated field so we can determine how we want to return dates.

ifelse(
${TimePeriod} = 'Monthly' AND truncDate('MM', ${StartDate}) <= truncDate('MM', {Date}) AND truncDate('MM', ${EndDate}) >= truncDate('MM', {Date}), {Date},
${TimePeriod} = 'Weekly' AND truncDate('WK', ${StartDate}) <= truncDate('WK', {Date}) AND truncDate('WK', ${EndDate}) >= truncDate('WK', {Date}), {Date},
...etc.,
NULL
)

In order to return all values, we can convert the date parameter and your date field when making the comparison depending on the value selected in TimePeriod. This should help resolve the issue you are facing, but please let me know if you have any remaining questions.

5

When ${TimePeriod} = ‘Monthly’ I need to aggregate the data per month based on start and end date. However,
ifelse(
${TimePeriod} = ‘Monthly’ AND truncDate(‘MM’, ${StartDate}) <= truncDate(‘MM’, {Date}) AND truncDate(‘MM’, ${EndDate}) >= truncDate(‘MM’, {Date}), {Date}, etc)
it shows date per day as the ifelse then expression is {Date}.

I tried to change the ifelse then expression to truncDate(‘MM’, {Date} but it got me back to square one which is ignoring the start date if it’s not the 1st day of the month!

1 Like
6

Hello @abdulhzm, even when you return the date as truncDate(‘MM’, {Date}) and your start date is within January, it returns the first date as February?

Oh, I have a thought. Since you are checking the dates with calculated fields based on the parameters, you should remove date filtering based on the selected dates on the visual. It sounds like you have kept the filtering applied when you are already managing it in the ifelse statement. That should resolve the problem!