Challenges in creating Tabular Report in Tableau Using Table Calculations

SCENARIO:

We have one tableau model, which extracts patron rating data at a day level.

Tableau model has below columns only.
GAME_DAY, PATRON_NO, BET, WIN

One of our users tried to create this report using Tableau by keeping below conditions in mind.

1. Tableau workbook has to look for the gaming days that are consecutive for a patron number and count that as one "Trip".
2. Tableau workbook should be having a "Recent trip" filter to filter the records based on Recent Trip Number.  

Patron_no	Trip Start Date	Trip End Date	Recent Trip?	BET	WIN
~~~~~	1/9/2019	1/9/2019	4	$             58	$           100
	13/09/2019	14/09/2019	3	$           439	$           119
	23/09/2019	28/09/2019	2	$        1,278	$        1,931
	30/09/2019	2/10/2019	1	$          950	-$          856
			Sub Total	$          2725	$         1294

When any developer gets this kind of request from the user(s), the developer usually says that Tableau is meant to create visualisation charts/dashboards but not to create TABULAR reports.
But sometimes we can't deny the user request mostly because of the following reason(s).
1. If the report is compliance or regulatory report that customer has to submit every quarter to government bodies. Customer may not be interested to buy/use some other reporting tool just to create/view this kind of reports in another reporting platform like SSRS.
2. Developer wanted to make the user happy by getting the report done in Tableau instead of advising them that it may not be possible in Tableau.
3. The manager wanted to bill 😊 the customer (or user) by developing this kind of reports in Tableau with modifying the tableau model query as an enhancement.

I hear many times when the developer saying that he/she can develop this kind of report in very less time in SSRS or in another reporting tool (whichever tool he/she is expertise).

I have tried to develop this report in Tableau using Tableau Public to showcase that we can develop this kind of report in Tableau too. I managed to get it done to some extent.

I have followed the below approach to achieve this scenario.

1.  I have created a calculated field to get "Trip Start Date". Challenge#1 - This would expect Game_Day column must be added to the Rows to get the correct "Trip Start Date"
RUNNING_MAX(IF Ifnull((( MAX( [game day])) - (LOOKUP(MAX([game day]),-1))),0) <> 1 then 
max([Game Day]) 
END 
)
Please make sure to "Edit Table Calculation" of "Trip Start Date" to below

2. To create "Trip End Date" date for each trip, Challenge#2-  I ended up creating many calculated fields, which would navigate to previous game day and to check whether the patron has visited the venue and it would navigate to 4 previous days as I have created only 4 calculated columns. I know this is NOT the correct approach, there must be some easy approach that I am not aware of.
I have created below calculated fields in order to get "Trip End Date"
[No Of Days] = ( MAX( [Game Day])) -(LOOKUP(MAX([Game Day]), 1)) 
[TE1] =IF [NO OF DAYS] <> -1 last() = 0 MAX([Game Day]) END 
[TE2] =IF NOT isnull([TE1]) THEN [TE1] ELSE ([TE1], 1)) END 
[TE3] =IF NOT Isnull([TE1]) 
then [TE1] elseif NOT isnull([TE2]) THEN [TE2] ELSE (lookup([TE2], 1)) END 

[TE4] = IF NOT Isnull([TE1]) 
then [TE1] elseif NOT isnull([TE2]) THEN [TE2] elseif NOT isnull([TE3]) THEN [TE3] ELSE (lookup([TE3], 1)) END 

[Trip End Date] = [TE4]

Please make sure to "Edit Table Calculation" of  all above calculated fields to below

3. I have created a calculated field to get Recent Trip NumberTrip End 

[Recent Trip?] = 

'TRIP ' + STR (

WINDOW_SUM(

IF  ATTR([Game Day]) = (RUNNING_MAX(IF  IFNULL((( max( [Game Day])) - (LOOKUP(max([Game Day]),-1))),0) <> 1 

THEN MAX([Game Day]) END)) 

 THEN 1 ELSE 0 END ) -

RUNNING_SUM(

IF  ATTR([Game Day]) = (RUNNING_MAX(IF  IFNULL((( max( [Game Day])) - (LOOKUP(max([Game Day]),-1))),0) <> 1 

THEN MAX([Game Day]) END)) 

 THEN 1 ELSE 0 END ) + 1

)

Please make sure to "Edit Table Calculation" of  "[Recent Trip?]" also like previous screenshots.

I expect to see highlighted rows as one row but since I cannot remove [Game Day] field from "Rows", I get detailed rows. Is there any way to get summarized row?

Below are challenges I have faced, couldn't figure out how to resolve in Tableau. 

Challenge#1 -  Since all calculated fields created on [Game Day],  Tableau expected to use [Game Day] in Rows and not merging the records based on other column values. Any Trick here?

Challenge#2 -  To get the Trip End date correctly, Is there any easy way to get it correct without creating many calculated fields.

Challenge#3 -  Subtotals of discrete measure are not supported. Disabling subtotals on "Trip End Date".

Below is the sample workbook published in the Tableau-public. Kindly let me know in case if anybody faced and resolved these challenges at the Tableau Desktop level without adding a key column (that distinguishes previous and current trip) in the underlying tableau model. 

Thank you,

Narasimha