1 2 3 4 5 Previous Next 69 Replies Latest reply on Feb 26, 2016 9:28 AM by Łukasz Majewski Go to original post
• 60. Re: Got a fun Educational Brain Teaser

Hmmm, I have an idea.

Looking at the axis, you are using a Discrete pill on the Columns Shelf, so I'm going to assume that the X-Axis (Pill on the columns shelf) is a String, which is how you are able to show the formatting.  But what is interesting is the fact that your Discrete Axis contains more Items than there is in the source. So I'm thinking Data Blending is a part of this solution.

In addition, the Jaggedness of the Lines leads me to believe that there are more than 8 Marks at play here, maybe further evidence supporting Data Blending, but I could be wrong.

I'll have a play at this later today and see if I can figure out the method you used.

But am I close in my assumptions?

Regards,

Rody

• 61. Re: Got a fun Educational Brain Teaser

Let me shed some light:

- columns pill is discrete (string)

- no blending is used

- status bar reads 36 marks

- the number of leading or trailing points is arbitrary and driven by two parameters

• 62. Re: Got a fun Educational Brain Teaser

Hey Lukasz,

Ohhhh ok I see what you did (I think!). I didn't consider the Parameters route.

Now the number of Marks makes sense.

Regards,

Rody

• 63. Re: Got a fun Educational Brain Teaser

Let me try and put this together:

First - as I do not like hard coded literals - I introduced these parameters (p1&p2 are integers and 'Reference Date' is date):

Then I created a date field called [Month Raw] just to facilitate further calculations:

```DATEADD('month', INT([Months Since String]), [Reference Date])
```

and actual dates used in the sheet as [Month Extended]:

```IF [Month Raw]={FIXED:MIN([Month Raw])}
END
```

the above may look strange but it is the first trick i.e. to split all the data into the overall MIN & MAX month.

But what we really want on axis are the strings [axis label]:

```IF index()<0 then 'X' //just to set the right addressing; always false
ELSEIF [last]<[p2] THEN 'Trailing' + STR([last])
//ELSEIF [index]<=[size]/2+1 AND [index]>=[size]/2-1 THEN ' Middle'+STR([size]-[index])
ELSE STR([index]-[p1]-1) END
```

Since nested table calculations will be used here I predefined these:

having these as separate fields enables independent addressing/partitioning settings.

Now the first step of sales aggregation [Sales Calc]:

```IF LOOKUP(SUM(INT([Months Since String])), 1-index()) = index()-[p1]-1
OR LOOKUP(SUM(INT([Months Since String])), size()-index()) = index()-[p1]-1
THEN WINDOW_SUM(SUM([Sales]))
END
```

and finally [Sales Final]:

```WINDOW_SUM([Sales Calc])
```

Now let's toss the pills around:

We have to make sure domain padding is on so I first put my months on columns, switched it on only to move it to marks:

followed by the native [Month Since String] in the marks and [axis label] on columns.

For [axis label] all the nested 'position' calcs are computed using [Month Extended]:

while actual [axis label] is using advanced combination:

I think the above calculation is the clue here as it switches on both domain padding and completion.

And Finally let's put [Sales Final] into rows with a bit easier combination: [Sales Calc] is using [Month Extended] and [Sales Final] is computed along [Month Since String].

And switch to lines.

Note order of the two pills in marks shelf - I do not understand why but it must be as follows - it is different than my previous picture shows:

wb 9.2 attached (see Sheet 3 or 4)

2 of 2 people found this helpful
• 64. Re: Got a fun Educational Brain Teaser

and some fun with stack marks on

• 65. Re: Got a fun Educational Brain Teaser

Wow that's impressive!

Thank you for sharing, I'm going to be spending a lot of time going through your workbook. There are a few techniques I haven't seen before.

Best regards,

Rody

• 66. Re: Got a fun Educational Brain Teaser

I learned a lot from this video by Joe Mako:

Tableau Request Live - Data Densification on Vimeo  - especially the twist with advanced addressing when my months are in marks shelf.

I would be happy to see explanation of why dimensions order in marks shelf matters here...

• 67. Re: Got a fun Educational Brain Teaser

Hey Lukasz,

Yes that is a great reference video, by the "Original Zen" by Joe Mako

What is interesting is the use of the LoDs in the formulas to do this. It looks like you "sort-of" created a Scaffold, without actually creating a Scaffold Source that is blended to.

I haven't quite seen that technique done before.

Regards,

Rody

• 68. Re: Got a fun Educational Brain Teaser

Hi ŁUKASZ,

You may turn Text Labels to On

for your [Month Since String] on Details

and see what's happened when you shuffle pills.

Actually, all your marks are there,

but the lines becomes "jammed"

because of Stack Marks is Off.

Hope this could help.

Yours,

Yuri

• 69. Re: Got a fun Educational Brain Teaser

OK, I see. But what is going on - line path is affected or marks overlapping? It is just weird to me...

Edited:

It is the Path and we may change it with inverted order:

Thanks Yuri

1 of 1 people found this helpful
1 2 3 4 5 Previous Next