My initial thought would be to use two separate worksheets and leverage the INDEX() and SIZE() table calculation functions to tell Tableau which rows to place in which worksheet.
I've attached a revised copy of the workbook where I duplicated the CustomerList worksheet and created a 'row filter' calculated field that uses the following formula:
[index] <= ([size] / 2)
The calculated field is then placed onto the Filters shelf and set to show True values for CustomerList col1 worksheet, and False values for CustomerList col2 worksheet. Additionally, the 'row filter' calculated field has the advanced Compute using settings configured by right-clicking the field on the view and selecting Edit Table Calculation. I have then selected Advanced for Compute Using and set Customer ID and Customer Name under the Addressing column.
By using ([size] / 2) as the formula to filter with, we can force the first column to show the first half of the rows, and the second column will display the second half of the rows. As long as the client wants to always split the rows equally between the two columns, this configuration should accomplish that.
If there are any questions let me know. Thanks David!
DualList_REVISED.twbx 1.0 MB
Dan the man!
This solution is perfect for what we need. Thank you!
I just had to fiddle around to get the advanced compute feature going for Index and Size function. So this is now working quite neatly in the real workbook.
Just wondering how might we be able to tweak the [index] <= ([size] / 2) formula so that when we have an odd number size, the +1 row will fall into the first worksheet? E.g. If we have a size 21 list, how can we force the first 11 to go into sheet 1 and the last 10 in the second? At the moment 10 are going into sheet 1, and 11 into sheet 2. It would be good to keep the order shown correctly
Tinkered around with the formula - seems like the below puts the higher row count into first sheet. Maybe it can be simplified? either way, it works for me.
IF ([size]%([size] / 2))= 0
THEN [size] / 2
ELSE [size] / 2 + 0.5
That's a great question and one that requires a fun solution!
In the sample workbook I took the SUM(Number of Records) field off of the Filters shelf and noticed that we have an uneven number of Customer IDs which was great news as I did not have to create a new data source!
So it looks like that data source has 3403 Customer IDs, so we need to show 1702 in the first column and 1701 in the second column.
For this type of issue we can use the Modulo operator (%) to check whether the total number of Customer IDs in the view is Even or Odd by using the the following formula:
"[size] % 2"
For values that are Odd the above formula will return a value of 1 and if the value is Even, the above formula will not return anything. With this in mind, we can modify our 'row filter' calculated field to use a slightly different formula as below:
IF [size] % 2 = 1
THEN [index] <= ([size] / 2) + 1
ELSE [index] <= ([size] / 2)
We will then place the new calculated field (I have named mine 'row filter + 1 on first sheet' in the new workbook) onto the Filters shelf and use the same Compute Using settings as before. The col1 worksheet will set the Filter value to show True and the col2 worksheet will set the Filter value to show False. Let me know if there are any questions with the new filters.
DualList_REVISED v2.twbx 1.1 MB
Aah! So that's how the % mod works in Tableau. I was getting confused, expecting it to return an actual value rather than a flagged response
You've explained that really well and provided a neat formula which I've now changed to!
Again, thank you for your help Dan.
hello David, can u help me,, i need to separated the title of specific date , for example 1100-0000 represent 24 January and 0100-1000 represent 25 January, how to do so David. and another question, i need to to change the number 0000 for 2400, can u help me please David
I'm not sure if I can help you with the translation of numbers into dates? Have you tried starting a new post and putting together a packaged workbook that demonstrates the data? I'm sure there will be someone within the Tableau community who can help providing more information is provided