13 Replies Latest reply on Feb 26, 2016 11:19 AM by ChandraSheker A

Calculating Numbers of dates with specific amount in last 30 days.

HI, Below is a sample date to which i am trying to apply logic. Can find attachment for the sample excel.

I am trying to get the logic in tableau:

If an ID has a Fee of -35 then it is Late fee, so i have to count the number of late fees for an ID.

and I want to also see the number of late fees in last 30 days.

In above pic if you can see, ID has three -35, that means that ID has 3 late Fees and that too in last 30 days.

am i want to see Finally,

irrespective of the view,

ID ,count( Late fees),  and if the count of Late fees are more than or equal to 2 i have to say that ID is not good else good.

*******Or else Simply, I have to see the number of late fees for an ID in last 30 Days.

Note: Fee in above shows -35, it can be any number like -50,399,373,22,334, etc like below pic. But I have to consider only Fees with -35 and count them.

Hopefully i am clear in explanation.

• 1. Re: Calculating Numbers of dates with specific amount in last 30 days.

Hey CJ-

Give this a look and see if it works.

9.2 workbook attached.  Call out if you have any questions.

• 2. Re: Calculating Numbers of dates with specific amount in last 30 days.

Oh and you need to unmerge those cells in your excel workbook and get the values on every row.

• 3. Re: Calculating Numbers of dates with specific amount in last 30 days.

Thanks for your Time.

I am a bit confused in applying your way of approach.

i am trying to apply in my source which is not an excel.

I am unable to figure out of this.

i want exclude The ID's which have more than two -35 ( amounts) in last 30 days.

• 4. Re: Calculating Numbers of dates with specific amount in last 30 days.

To exclude that way then

Filter Date Bucket = Within Range

SUM(Late Fees) At most 2

• 5. Re: Calculating Numbers of dates with specific amount in last 30 days.

Thank you.

If you can see above pic for ID 82569,82225 the late fees counted as 2, Whats making it count 2?      I am using Exact Dates.

can i know why are we using status in above? should i filter this aswell?

is your calculating for late fee same as below calculation? you have used IF [Amount] = -35 THEN 1 END

But i have used { FIXED [ Id],[Date],[Name]: SUM(IF [Name]='Fee' and [Amount]=-35 THEN 1 END)}

But still i am not excluding the ID which have -35 fee more than 2 times in last 30 days.

I am looking now for above  ID 89286, I have applied all filter you suggested.

By when i investigate this ID i can see it has -35 fees in last 30 days as well.

That means i should have not for this ID

• 6. Re: Calculating Numbers of dates with specific amount in last 30 days.

I will try to send you another excel file with few more IDs that way i can make you more clear what i am looking for.

• 7. Re: Calculating Numbers of dates with specific amount in last 30 days.

CJ-

I wrote those formulas to meet the needs you described in your first post.

Late Fees - Returns a count a late fees

Status - Returns a "Not Good" if the ID has >= 2 late fees - regardless of view

Date Bucket - determines whether a record is within the days specified from today by the parameter.

It looks like you changed the late fee formula to a fixed LOD.  I can't see your filters so I am not sure what it going on.

Can you share the workbook?

• 8. Re: Calculating Numbers of dates with specific amount in last 30 days.

Please See below Attachment for a New Excel, I have included many IDs' Now in it.

My final goal is to Filter out the ID's Which have More than two -35 fees in last 30 days.

• 9. Re: Calculating Numbers of dates with specific amount in last 30 days.

I can't share workbook i am using live connection for development.

But my data resembles above excel file.

Please let me know if i can make it more clear.

• 10. Re: Calculating Numbers of dates with specific amount in last 30 days.

Here you go - I had to do some excel work to ditch the merged cells again...

There is only one ID in this sample that has more than 2 late fees within 30 days.

1 of 1 people found this helpful
• 11. Re: Calculating Numbers of dates with specific amount in last 30 days.

To what should i refer now? in that workbook?

To make sure i dont have the IDs in the view which have the Fee -35 more than 2 times in last 30 days.

• 12. Re: Calculating Numbers of dates with specific amount in last 30 days.

CJ-

Set Exclude to "Keep" as shown on the first sheet.  That's it.  I built into one formula for you.

Exclude

IF {fixed [Id] : SUM(IF [Date] >= TODAY()-[Days from Today] AND [Late Fee] = "Late Fee" THEN 1 END)} > 2 THEN "Exclude" ELSE "Keep" END

There is only 1 ID that has more than 2 late fees within the last 30 days.  89286 has 15 late fees within the last 30 days.  Look at sheet 2 to see the breakout.  If you want the formula to be >= to 2 then you need to add an equal sign, but you asked for more than 2 so I just used >.

1 of 1 people found this helpful
• 13. Re: Calculating Numbers of dates with specific amount in last 30 days.

Thank you Adam, For all your time on this.

I changed it to >=2, For the Logic what i am looking for.

Now i have to see how i can apply into my View.

But Thanks a Lot again for spending time on this.

It really helped me.

But will let you know if i have any other issues applying onto my view.