Static ast 30 days

[shay]

I uploaded a table with columns date and spend.
In my viz I built a table which I want to show:

1. Date- according to date picker.
2. Spend according to the filter dates.
3. Spend of the last 30 days regardless of the dates I picked.

How can I create section 3?
I tried the below method but the date picker affected it:

1. First created a parameter StartDateB that starts 30 days before today.
2. Than tried this formula:
   ifelse(
   truncDate(‘DD’, {date}) >= ${StartDateB} ,
   {spend},
   null
   )

[Giridhar.Prabhu]

Hi @Shay,

Your approach seems right and I would expect you to see the data you want. What is the problem you are facing?

You can refer to the following article for more details for such computations.

Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)

Often you may want to create tables and other visuals which display multiple fields that are all ‘filtered’ to different date periods, as well as period-over-period fields. However we can’t use normal filters for this since filters get applied to all fields in the visual. Conceptually in this solution we are building the filters directly into the calculated field itself (using the ifelse logic), so that each field can have a different ‘filtered’ time period. You can then do whatever math you wan…

[shay]

Thank you’ I followed the article and the problem is not solved.
If I change the date in the model, it affects the start date for ‘Spend of the last 30 days’ field:
ifelse(dateDiff(date},now()) <=30, {spend},0)

Example:

1. Current date is 11/14/2023.
2. In my date filter I chose the last 7 days.
3. The value for the field ‘Spend of the last 30 days’ is starting at 11/07/2023 as in the date filter even though it is a calculated field.
4. I would expect that ‘Spend of the last 30 days’ won’t be affect by the dates I pick in the model.

[Giridhar.Prabhu]

Your expectation is right.

Can you share the details of your ‘Spend of the last 30 days’ calculated field?

[shay]

My source table has the following fields:

1. date.
2. spend.

The calculated field ‘Spend of the last 30 days’ is made by this formula:
ifelse(dateDiff(date},now()) <=30, {spend},0)

I tried using a different formula but encountered the same problem:
ifelse(
truncDate(‘DD’, {window_time_frame[query]}) >=addDateTime(-30, ‘DD’, now()),
{spend_breakdown},
null
)

[Giridhar.Prabhu]

Hi @shay

You should not filter the visual with your date field. Instead your calculated fields shoud be computed based on the parameters.

I created a small case for you to look at

There is no filter applied for date on my left visual. The two calculated fields are as follows

Hope this helps

[Brett]

Hi @shay,
It’s been awhile since we last heard from you, did you have any additional questions regarding your post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

[Brett]

Hi @shay,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!