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

Question & Answer
, ,
1

I have a week filter applied to a line chart and I want to see the week start date and end date when I export to CSV.
Currently It is showing only the week start date in the CSV file and also is it possible to display the same on x-axis and also while hoover on the visual.

I tried with the below field.

ifelse(${PeriodStarting} = ‘Day’, truncDate(‘DD’,{AS_OF_DATE}),
ifelse(${PeriodStarting} = ‘Week’, truncDate(‘WK’,{AS_OF_DATE}),
truncDate(‘MM’,{AS_OF_DATE})
))

the below is the CSV, after exporting

Report Date (Period Control) Approved Amount (SUM) Transaction Count (SUM)
4/28/2024 0:00 12345566 29156
4/21/2024 0:00 1234331 28556

Here, can we see the dates like 4/21/2024 to 4/27/2024 in report date column. similarly 4/28/2024 to 5/4/2024, when I choose a week filter.

Thanks!

2

Hi @praveen.gp, to display the week start date and end date in the CSV export, you may need to modify the dataset.

1. Modify the Dataset

First, ensure that your dataset contains both the start and end dates for each week. You can do this by creating calculated fields.

a. Creating Calculated Fields for Week Start and End Dates

Create two calculated fields in your dataset: one for the week start date and one for the week end date.

  • Week Start Date:
truncDate('WK', {AS_OF_DATE})
  • Week End Date:
dateAdd('DD', 6, truncDate('WK', {AS_OF_DATE}))

2. Combine Week Start and End Dates

Create a third calculated field that combines these two dates into a single string.

  • Week Range:
concat(
  formatDate(truncDate('WK', {AS_OF_DATE}), 'MM/dd/yyyy'),
  ' to ',
  formatDate(dateAdd('DD', 6, truncDate('WK', {AS_OF_DATE})), 'MM/dd/yyyy')
)

3. Update Visualizations

a. Update the X-Axis

Use the Week Range calculated field on the x-axis.

  1. Go to your visual and select the x-axis.
  2. Replace the existing date field with the Week Range field.

b. Update Tooltip

To show the week range when hovering over the data points:

  1. Go to the visual and click on the Tooltip icon.
  2. Add the Week Range field to the tooltip.

4. Update CSV Export

Ensure that the Week Range field is included in your CSV export.

  1. In the Fields list, include the Week Range field in your analysis.
  2. When exporting to CSV, ensure this field is part of your dataset columns.

Example of Updated CSV

After these changes, your CSV should look like this:

Report Date (Period Control) Approved Amount (SUM) Transaction Count (SUM)
4/21/2024 to 4/27/2024 12345566 29156
4/28/2024 to 5/4/2024 1234331 28556

By creating calculated fields for the week start and end dates and then combining them into a single field for display, you can achieve the desired format in your visualizations and CSV exports. This will allow you to see the date range on the x-axis, in the tooltips, and in the exported CSV file.

We hope this solution worked for you. Let us know if this is resolved. And if it is, please help the community by marking this answer as a “Solution.” (click the check box under the reply)

2 Likes
3

Thank you @Xclipse for the information. This works.

But, In my case I also want to add Day, week, Month range filter using the parameter like I gave the field above. So, if I choose day, it have to show the data on date wise and when I choose week it has to show the week data like you gave the solution(week start date to end date) and similarly with month.
Any help here would be much appreciated.

image

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