7 Replies Latest reply on Mar 27, 2016 9:51 PM by Mahfooj Khan

[Urgent Help] How to create a rolling countif in Tableau

Hello All!

I need help to sum up the last three months based on my selected month, across all years.

Now the challenge is that if I select either January/February, then it should sum up November last yr + Dec last year + January if the selected option is January and if February then Dec last yr + January + February.

I was able to create a fixed LOD calculated field based on my category and the date wherein we are going to sum the total across months if the Final Difference is between 0-2.

The problem is it's not summing up correctly, which I believe is because I fixed it on the Month and Year.

What I'm thinking is to create a group for each 0,1,2 occurrences, somewhat like a rolling countif in Excel.

Is it possible to do in Tableau?

• 1. Re: [Urgent Help] How to create a rolling countif in Tableau

Hi,

Create a string months parameter like this from Jan-Dec

Use that parameter to calculate month data

Create calculated field

then create  a LOD to calculate last three month data.

If you select month Dec it will show you last 3 months data i.e Oct,Nov and Dec

You can use this calculation to get the idea for your requirement.

If you want the sum of all the three months then drag the months parameter in row shelf and LOD in label.

Like this

Mahfooj

• 2. Re: [Urgent Help] How to create a rolling countif in Tableau

I already have done this however it won't work if my selected month is January or February, similar to what I have previously done.

Is there any way to capture that in Tableau? Thank you in advance!

• 3. Re: [Urgent Help] How to create a rolling countif in Tableau

May I know also what the calculation is in your Calculations5 Field? Thanks!

• 4. Re: [Urgent Help] How to create a rolling countif in Tableau

I wanted to create something like this.

if I do the rolling countif in Excel. In this case, the month selected is January. Instead of fixing it on the Order Date, it should be fixed on the Counter/Group.

However, I'm stuck on how to do it in Tableau...

My Excel formula looks like this, where I have a rolling counter to group the values based on the Difference. Hope this clarifies further.

=IF(D7="","",IF(\$C7=0,COUNTIF(\$C\$3:\$C7,\$C7)-1,COUNTIF(\$C\$3:\$C7,\$C7)))

• 5. Re: [Urgent Help] How to create a rolling countif in Tableau

Try this calculation

I've attached the workbook.

Mahfooj

• 6. Re: [Urgent Help] How to create a rolling countif in Tableau

Downloaded your file. When I selected January it only shows January. It should also show November and December of last year (2012 an exception).

Is there any other way? Appreciate your help so much, Mahfooj!

• 7. Re: [Urgent Help] How to create a rolling countif in Tableau

Its because we don't have data for year 2009 that's why!

Just removed the Jan & Feb from the month parameter list so that you don't get confused.