When applying a week filter to a line chart. Can we see the week start date and end date in CSV

Question & Answer
, ,
4

Hi @praveen.gp, to create a dynamic date range filter that updates based on the selected period (Day, Week, Month), here is a high-level idea:

Step 1: Create the Parameter

  1. Go to your analysis.
  2. In the left panel, click on the “Parameters” tab.
  3. Click “Create parameter” and set it up as follows:
  • Name: PeriodControl
  • Data type: String
  • Values: Day, Week, Month
  • Control type: Single value (dropdown)

Step 2: Create Calculated Fields

Create calculated fields for each period type.

  1. Calculated Field for Displaying the Date Range:

pseudocode - correct with actual syntax

ifelse(
    ${PeriodControl} = 'Day',
    formatDate({AS_OF_DATE}, 'MM/dd/yyyy'),
    ifelse(
        ${PeriodControl} = 'Week',
        concat(
            formatDate(truncDate('WK', {AS_OF_DATE}), 'MM/dd/yyyy'),
            ' to ',
            formatDate(dateAdd('DD', 6, truncDate('WK', {AS_OF_DATE})), 'MM/dd/yyyy')
        ),
        concat(
            formatDate(truncDate('MM', {AS_OF_DATE}), 'MM/dd/yyyy'),
            ' to ',
            formatDate(dateAdd('DD', dateDiff('DD', truncDate('MM', {AS_OF_DATE}), dateAdd('MM', 1, truncDate('MM', {AS_OF_DATE}))) - 1, truncDate('MM', {AS_OF_DATE})), 'MM/dd/yyyy')
        )
    )
)
  1. Calculated Field for Aggregation Period:

pseudocode - correct with actual syntax

ifelse(
    ${PeriodControl} = 'Day',
    truncDate('DD', {AS_OF_DATE}),
    ifelse(
        ${PeriodControl} = 'Week',
        truncDate('WK', {AS_OF_DATE}),
        truncDate('MM', {AS_OF_DATE})
    )
)

I am marking this reply as, “Solution,” in case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena