Rolling days comparison

[InbalK]

Hello,

I use this KPI visual:

I want to compare between current 7 days to the previous 7 days (not calendar weeks)

How should I do that?

Thanks

[andrepgn]

Hi @InbalK ,
you cannot obtain it directly from the KPI, but we can create a workaround.

Given that we always need the comparison from TODAY, we can create a calculated field which provide us how many weeks from today we are:

// Given that the date field is called "date"

WeekDiffFromNow = dateDiff( truncDate('DD', date), truncDate('DD', now() ), "WK")

You will have something like this in as result:

Now we can take advantage of that calculated number to aggregate our data to single days of week creating another calculated field:

DaysAggr = addDateTime(-WeekDiffFromNow*7, "DD", truncDate("DD", now()))

We have aggregated the data in a single day which is distant from today in multiples of 7, so if we now create the KPI visual, you should obtain what you wanted in the beginning, selecting DaysAggr as Trend Group, instead of the initial date field.

These are the two visual in comparison. The first one is using the original date as trend group, and if you select Week as aggregation, it automatically calculates the diff between entire weeks.
The second one we use our calculated field DaysAggr instead, but just be sure to select DAY as aggregation instead of Week!

Hope this can be a workaround that fits for you,
have a nice day!

Andrea

[InbalK]

Thanks!! It works!
I don’t want to include today, the 7 days should be from yesterday
What should I do?

[andrepgn]

If you want to only exclude today, you can add a filter like this on your original date field on the KPI visual.

Instead, if you want to start counting the days from yesterday, we need to change the formulas in this way:

WeekDiffFromYesterday = dateDiff(
    truncDate('DD', date),
    truncDate('DD', addDateTime(-1, "DD", now() )),
    "WK")

Then

DaysAggr = addDateTime(-WeekDiffFromYesterday*7, "DD", truncDate("DD", addDateTime(-1, "DD", now())))

And still need to use the filter in the KPI graph as shown above!

This should work

Andrea

[InbalK]

Thank you! Very helpful
Now I try to do the same for an insight visual:

How should I fix this error?
thank you

[andrepgn]

The error seems to indicate that you are comparison two periods and for at least one of this you don’t have data, or you have used a field in the period over period that you haven’t selected in the specific visual.

Have a check on this part

Andrea

[InbalK]

Thanks, I try to do the same for 30 days and 90 days:
MonthDiffFromNow=dateDiff( truncDate(‘DD’, date), truncDate(‘DD’, now() ), “MM”) 30DaysAggr=addDateTime(-{MonthDiffFromNow}*30, “DD”, truncDate(“DD”, now()))

It doesn’t work well, it is calculates by calendar month and not by days

What is the problem and how should i fix this?
Thanks
@andrepgn

[WLS-Luis]

Hi @InbalK,

Hope everything is well! Just checking in since this thread hasn’t received a response in a while. Were you able to find a workaround yourself in the meantime or are you still facing persistent issues? If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

[InbalK]

All good now, thanks!