Ctrl drag from Label onto the Rows shelf.In the Sort Order dropdown, select Custom, Order Date, Minimum, Descending.Under Nested Calculations, select End Date.Under Nested Calculations, select Next Profit Value.Under Nested Calculations, select Profit no gaps.Check only Month, Day, Year of Order Date.Under Compute Using, select Specific Dimensions.Under Nested Calculations, select Start Date.In the Table Calculation dialog, do the following:.Right-click on Label and select Edit Table Calculation.In the Drop Field dialog, select MDY(Order Date) and click OK.Right-click and drag to Detail on the Marks card.Create a calculated field with a name like "Keep only one date per streak group" with a calculation similar to the following:.For more explanation on why see the article Using an Aggregate Field to Define the Level of Detail in a Table Calculation Note: The calculated fields and must include a condition for every possible number of days in a streak. Create a calculated field with a name like "# of Streaks" with a calculation similar to the following:.Create a calculated field with a name like "Days in Streak" with a calculation similar to the following:ĭATEDIFF('day',, ) + 1.Create a calculated field with a name like "End Date" with a calculation similar to the following:.Create a calculated field with a name like "Next Profit Value" with a calculation similar to the following:.Create a calculated field with a name like "Start Date" with a calculation similar to the following:.If you skip this step use SUM() rather than in all following steps.
Note: If the data set is missing days, then this step will allow us to treat missing days as having zero profit. Optional: Create a calculated field with a name like "Profit no gaps" with a calculation similar to the following:.Then we can find end date with a similar process but reading the data from oldest to newest date. Using the PREVIOUS_VALUE() function, we can repeat the starting date value for every day in the grouping. We can find the starting date for a group of consecutive days by checking if the current day meets the condition (has negative profit) and the previous day does not. Option 1: Use PREVIOUS_VALUE() in Tableau Desktop For example how many 3-day streaks of negative profit are in the data set? The workbook also includes instructions for creating a crosstab view and a Gantt chart view. These directions create a highlight table that counts the number of streaks in each grouping. If there are 3 days in a row with negative profit, those days would all belong to group "3"ĪnswerThe following instructions, using the sample data set Superstore, are demonstrated in the workbook "running count groups_v2021.1.twbx" which is downloadable from the right-hand pane. QuestionHow to group consecutive values that meet a condition, or in other words all the values that belong to the same streak.įor example, creating groups of days based on the number of consecutive days that had a negative profit.
0 kommentar(er)
