Incremental refresh & last X days

Q&A
,
1

Hello,
I have a dataset with an incremental refresh every hour, and I want the dataset dates always to be the last two weeks from the current date.
How can I do this?

Thanks in advance,
Mia

2

to be clear you want the dataset to be curtailed, e.g. to save spice storage, or are you talking about filtering a visual to only show the most recent fortnight?

for the former (i have not tried this myself) if you:

  • go to datasets
  • and select the dataset in question,
  • then go to ‘edit dataset’,
  • then create a calculated field: is_newer_than_14d as
    ifelse(dateDiff({date_field}, now(), 'DD')<15, TRUE, FALSE)
  • then choose filter from the left hand side gutter,
  • filter on the new created field is_newer_than_14d choose ‘equals’ comparator, and enter value ‘1’
  • i would now expect that the data is curtailed at source, so whatever analysis draws on it will only see the most recent transactions.

hope this helps.

1 Like
3

Hi,
Thanks for your response.
Because it’s an incremental load the calculated field always be TRUE.

4

Hi @miap,

If your intent is to drop older records from the dataset, it can’t be handled with just incremental refresh. You can add filter in analysis layer to pull only last 14 days data into your visuals. However, to drop the older data from SPICE, you will have to do a full refresh in between.
Look at scheduling the full refresh once a day/week/month (based on what makes most sense for your use case and data volume.)

Regards,
Arun Santhosh
Pr QuickSight SA

2 Likes