10 Replies Latest reply on Oct 9, 2016 2:29 AM by Yuriy Fal

# Data Points on a Chart Not Connecting Sequentially Because of Unavoidable Discrete Item on Detail Shelf

What is My Data?

I've got a simple excel file of sports teams, month on which a hypothetical measure is taken and the measure.

1)     For each month, i must find the top ranked team as well as the 3rd ranked team

2)     Perform the following calculation of #1/#3 to get a ratio

3)     Plot the ratio over time

In my attached workbook,

1) Tab 1 (Data) gives a visual representation of the data

2) Tab 3 (Why is Marks of Line Inaccurate) shows that i have successfully identified top 1 and #3 over time. However, when i change the mark type to Line, tableau seems to be getting crazy

3) Tab 4 (I have the ratios, but Line isn't drawing) is similar for tab 3 except i actually have the ratios

4) Tab 5 (Ratios based on teams) shows why the lines are broken. They are broken by the unique teams

I discovered that lines are drawn uniquely based on the teams. See screen shot below

Que: Is this the best approach to solving this problem?

How else can i approach this problem?

Thank you

• ###### 1. Re: Data Points on a Chart Not Connecting Sequentially Because of Unavoidable Discrete Item on Detail Shelf

Hi, Franklin

[Score at 1st]

{fixed [Month]:max([Score])}

[Score at 2nd]

{fixed [Month]:max(if [Score] < [Score at 1st] then [Score] end)}

[Score at 3rd]

{fixed [Month]:max(if [Score] < [Score at 2nd] then [Score] end)}

[Ratio]

[Score at 1st]/[Score at 3rd]

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Data Points on a Chart Not Connecting Sequentially Because of Unavoidable Discrete Item on Detail Shelf

Nice, Shin.

I really like how you've adopted LOD calcs

in a case which a RANK() is commonly used for.

Though not as "dynamic" as the latter one

(where one could define both lowest and highest Rank

via Parameters, for example), but much more simple, indeed.

As for the RANK() approach, I would like to complement.

As one could see, it's much more complicated to build a view.

Hope it would help to get the whole picture.

Yours,

Yuri

• ###### 3. Re: Data Points on a Chart Not Connecting Sequentially Because of Unavoidable Discrete Item on Detail Shelf

Shin,

This is an amazing solution. It will definitely do for my problem. What i did not mention in my question is that I am interested in 1st rank, 3rd rank, 10th rank and possibly higher ranks. This approach will require that i find every single rank up to the one of interest. But like i said, it will do.

• ###### 4. Re: Data Points on a Chart Not Connecting Sequentially Because of Unavoidable Discrete Item on Detail Shelf

If you need more ranks like 10th, follow Yuri's approach.

Mine is feasible may be up to three or so.

Thanks,

Shin

• ###### 5. Re: Data Points on a Chart Not Connecting Sequentially Because of Unavoidable Discrete Item on Detail Shelf

Cascading FIXED LODs would result in

nested sub-selects to the underlying DBMS.

This is less a problem with TDE, though.

• ###### 6. Re: Data Points on a Chart Not Connecting Sequentially Because of Unavoidable Discrete Item on Detail Shelf

Yuri,

i like you approach better. It will allow for different rank levels. Also, your approach will allow for the drill down in the date filter.

Yuri: In the sample data i sent, the date field is uniform. In that, every single one is in the form mm/01/yyyy. ie Every record is on the first day of the month.  When i have tried your approach with varying dates, eg 01/01/2016, 01/15/2016, 03/01/2016, 03/04/2016 etc, this breaks your current solution. I could alter the date field and keep them in the form mm/01/yyyy. However, this takes away  the flexibility to see the chart at a more granular level. Do you think there is a way around this?

• ###### 7. Re: Data Points on a Chart Not Connecting Sequentially Because of Unavoidable Discrete Item on Detail Shelf

Hi Franklin,

I don't observe any problem with setting the [Date] --

that Green Pill on Columns -- at the required granularity,

up (down actually :-) to the Exact Date, if you wish.

The fine point is to keep the [Date] Pill a Continuous (Green) --

this is to have only one Pane on a view -- with Lines connecting Dots.

Could you please share a sample worksheet with a broken view?

Yours,

Yuri

• ###### 8. Re: Data Points on a Chart Not Connecting Sequentially Because of Unavoidable Discrete Item on Detail Shelf

What i mean is that at the underlying data level, the dates are not always uniform. they are not always the first of the month. Within the same month, you can have 1st, 2nd all the way to the last day of the month. In the screen shot below, i have circled March 11 and Feb 2. In the month of March, there is at least March 11 and March 1. This is causing the lines to break

For the sake of others that may find this useful, i have found a way though. I created a new combined field of teams and date(month/year). [Rather than the original teams and date combined field] this takes care of the problem on sheet 8.

• ###### 9. Re: Data Points on a Chart Not Connecting Sequentially Because of Unavoidable Discrete Item on Detail Shelf

Hi franklin,

I've completely missed the point

So the rule of thumb would be like this:

To get Lines connecting Dots on a Pane

one would use on a Path Shelf

a (properly sorted) Combined Field

consisting of all Dimension pills on a view

at their respective granularity (think of Dates).

Alternatively (instead of a Combined Field / Set)

it would be a Calculated Field

like STR(Dim1) + STR(Dim2)...

concatenating all necessary Dimensions.

Thank you again for pointing this out.

Yours,

Yuri