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.
Edit: just realize , I miss the consecutives part of the requirements.
looking at it....
Book2 (mc).twbx 81.9 KB
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
Book2 (mc2).twbx 81.8 KB
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
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.
last day nested calc.twbx 63.4 KB
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!
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.
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
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
but SQL doesn't handle mapping very well
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.
I had done something similar last week - and that one took a lot longer than 45 min
Have a good weekend