10 Replies Latest reply on Mar 15, 2019 9:02 AM by Jim Dehner

# Consecutive Days Target Met w. Aggregate

Hi Everyone,

I'm building a viz based on Transaction Ratio or percent compared to calls (Transactions/Calls or sum(Transactions)/Sum(calls) for multiple days). The goal is to have 35% of our calls result in transactions per day. I've been asked to provide a standalone number at the top of the viz that shows the current number of days the business has hit that target. I'm having issues finding way to do this so it's just a standalone number. All the solutions I've tried, I usually end up with one of two problems. Either I remove the Date from the viz and that removes the consecutive date counts or the count totals the consecutive days and doesn't reset when the goal is missed.

For instance, based on my current data, 3/12/2019 is our most recent date. Because we made the goal on the 11th and the 12th, my number in the Viz should be "2 consecutive days". If we made the goal on the 13th, once that's added to the data set, it would be "3 Consecutive Days". If we miss on the 14th, once that's added to the data set, we'd be back to 0 consecutive days.

Attached is some test data in a twbx. I'm in version 10.5. I do know how to do this in the data itself, so if this requires something that is too "brute force" then I might just put this in the data. But I'm interested if Tableau can do this.

Thanks!

• ###### 1. Re: Consecutive Days Target Met w. Aggregate

Corrie,

Check in the attached,

I made a copy of your calculation  Was Target Met,  but returning 1 or 0.

Then I do a Window Sum to get the number you are looking for.

Now, you have the number on every  Date row,  so i calculate a filter first() = 0  returning true only on the first row.

You can see the result of those calculations in the sheet validateData.

In the sheet finalView, I only keep Date on the Rows  but I right click the pill and unselect Show Header.

And I put the filter  and keep the True value, which give you a single copy of the number in the view.

Michel

Edit: just realize , I miss the consecutives part of the requirements.

looking at it....

• ###### 2. Re: Consecutive Days Target Met w. Aggregate

is this better ?

you can use the previous_value function to compute the consecutiveness...

if [Was Target Met? (copy)] = 0 then 0 else [Was Target Met? (copy)] + PREVIOUS_VALUE(0) end

When its 3  flag the row with one

if [Calculation1] = 3 then 1 else 0 end

and WindowSum this last calc

WINDOW_SUM( [Calculation2] )

pay attention this last one is a nested calculation, you have to set the computing on both  nested calculation to Table Down

• ###### 3. Re: Consecutive Days Target Met w. Aggregate

see the attached

and yes this is definitely brute force

it is well down the list of nested table calculations

this just places a counter on was met

this one is tricky it adds UP the consecutive Met days

then because you need to count down we reverse the calc

just calc down

this will get the last day

the max just accounts for a ) on the last day - this would return a -1 if it wasn't there

create the viz

now you need to hide a lot of things

and you end up with this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Consecutive Days Target Met w. Aggregate

Hi Michel,

It looks like this is returning the wrong answer - it says 5, but the consecutive days should be 2. It looks like Jim has a solution, but it looks pretty intense... Thank you for the help!

• ###### 5. Re: Consecutive Days Target Met w. Aggregate

Thanks Jim! I saw you post this in a different thread, but I couldn't quite get it to work for me. I think at the point I was at when I was trying it, I was still using a sum on transactions and calls to get the transacted ratio and by the time I realized that for one day I could get that number unaggregated i was pretty fed up. This is extremely brute force, so I might just add it to my dataset. I am a data dev by trade, so I have full control over the view on my server.

• ###### 6. Re: Consecutive Days Target Met w. Aggregate

Thanks Corrie

this type of problem is frustrating because there is not single calculation that will get you there - I have 2 others posted on my Public page - one is breaking customers into sale quintiles - the other is counting consecutive days then ranking them to find the 3 longest string - If you don't mind I think I will add this -   Tableau Public

Jim

• ###### 7. Re: Consecutive Days Target Met w. Aggregate

It is frustrating, Tableau is so powerful sometimes the simple things take a lot to get the result. I put this in the SQL - I basically took the max date of the last miss and did a date diff on the total max date and cross joined it back to my query. We're only interested in the current consecutive days, so this repeats the number in the dataset, but that's fine. Not a very complex solution to get it added to the data at least

• ###### 8. Re: Consecutive Days Target Met w. Aggregate

but SQL doesn't handle mapping very well

Jim

• ###### 9. Re: Consecutive Days Target Met w. Aggregate

Corrie,

That's what happens when I try to rush a question before leaving the office, I misread the requirements.

5 is the number of times you had at least 3 consecutive days meeting the criteria.

Yes Tableau can be frustrating sometimes, and having control on the database through custom queries, store proc or views is surely a plus. This way you can distribute your logic between Tableau and SQL.

But learning to use Tableau's table calculations, and lod calculations is also a big plus .  It took Jim less than 45 minutes to find the solution.

• ###### 10. Re: Consecutive Days Target Met w. Aggregate

Hi Michael

I had done something similar last week - and that one took a lot longer than 45 min

Have a good weekend

Jim