9 Replies Latest reply on Sep 15, 2016 11:37 AM by Alex Braun

# Comparing Number of Records within Rolling 12 Months

Hello,

My data is currently using Number of Records to count the number of issues reported by a computer user.

For example, for the previous rolling 12 months...

08/01/2015 - 07/31/2016 = 5000 Issues

09/01/2015 - 08/31/2016 = 4000 Issues

Now let's say historically since 2011, there have been 150,000 issues. The 5000 and 4000 figures are simply the number within that rolling 12 month period.

Is there anyway to count the number of issues within a given time frame (rolling 12 months) and compare the change into the next period? Ideally, I would love to be able to have a count of the historical number of issues, count of issues in current rolling 12 months, count of issues in previous rolling 12 months and the percentage change compared to the previous rolling 12 months.

Thanks!

Sam

• ###### 1. Re: Comparing Number of Records within Rolling 12 Months

Is your data source rolling? Or does it keep adding as time goes? If the latter, you could create a calculated field that says,

else 0

end

This will mark everything within the previous period as 1 and everything else as 0.

Thanks,

Alex

• ###### 2. Re: Comparing Number of Records within Rolling 12 Months

Hi Alex,

Thanks for the suggestion but I am running into errors while trying to use that formula.

I think a better way to rephrase is, is it possible for me to do a count of number of records within a given time frame? Like is there a formula that allow me to do SUM(Number of Records) between 07/01/2015 - June 31, 2016?

Sam

• ###### 3. Re: Comparing Number of Records within Rolling 12 Months

All that formula is doing is qualifying the data point to be within the specified period, you would then add the field to your view and do the sum and it should show the sum within the date.

• ###### 4. Re: Comparing Number of Records within Rolling 12 Months

It is giving me calculation errors regarding parenthesis but when I add parenthesis it still gives me errors. Is the formula you provided working when you paste it into Tableau?

Thanks!

• ###### 5. Re: Comparing Number of Records within Rolling 12 Months

Looks like I was missing a parenthesis at the end of the first dateadd, this shoul work now provided that you replace [Date] with your own date field.

else 0

end

• ###### 6. Re: Comparing Number of Records within Rolling 12 Months

hi Alex,

Thanks for the quick reply! It's working now, but I do have a question, in your formula how could I set it so I can type in specific dates?

• ###### 7. Re: Comparing Number of Records within Rolling 12 Months

You could either just type it in and say

[Date] >= '2015-08-01' and [Date] <= '2016-7-31' or you could create two date parameters so that you can have a start and end date that you can change.

Thanks,

Alex

• ###### 8. Re: Comparing Number of Records within Rolling 12 Months

Hello,

When I do that, the calculation is "valid" but the values turn into zeros even there is data in those date ranges.

Do you have any workbook examples using this formula?

Thanks!

Sam

• ###### 9. Re: Comparing Number of Records within Rolling 12 Months

Here is a quick screengrab showing the formula with use of parameters as compared to just a total sum of records.