1 2 Previous Next 27 Replies Latest reply on Jan 11, 2017 4:04 PM by Shinichiro Murakami Go to original post Branched to a new discussion.
• ###### 15. Re: Remove empty columns from year over year calculation

Hi Yuri

mmm,

Interesting trick...

This is not data filter but working as display filter.

Learning these things are pretty exciting to me, but at the same time, I sometimes hesitate to teach many people with these level of know-how.

Anyways, thank you for the great input always.

Thanks,

Shin

• ###### 16. Re: Remove empty columns from year over year calculation

Hi Shin,

I myself didn't know nothing

Just had discovered it by chance.

Don't know if anybody have had found it before.

Yours,

Yuri

• ###### 17. Re: Remove empty columns from year over year calculation

I might remember something in Brain teaser about putting something on dimension then move to detail.

This might be used in other some case as well, I think.

But I still think G.Total does not work with index filter is kind of bug?

Thanks,

Shin

• ###### 18. Re: Remove empty columns from year over year calculation
But I still think G.Total does not work with index filter is kind of bug?

We've discussed the same problem recently:

Is this a bug? Subtotals with table calculation filter

• ###### 19. Re: Remove empty columns from year over year calculation

Hi Shin,

Whether GTs filter table calcs or not is something that has gone back and forth between Tableau versions (I just learned about some of the history for this, it goes way back), this has been considered a bug or a feature at different times. There are use cases that argue for table calc filters affecting the GT and use cases that argue the opposite. (If you're interested in some details send me an email at jonathan (dot) drummey (at) gmail.)

In any case, I think of Yuri's solution (which I agree is pretty ingenious!) as taking advantage of Tableau's order of operations. Hide is the latest "filter" and does affect grand totals so the "trick" is in building out the boolean LOD expression that only returns True for the desired year and having the table calculation use Next instead of Previous. I agree, this gets complicated because it requires knowing table calcs, LOD expressions, and the order of operations to pull off.

My usual workaround in cases like this where there's just one YoY calc is to use LODs or Sets to identify the latest year and then build out record-level & regular aggregate combinations like SUM(IF year = latest year ...) and SUM(IF year = latest year -1 ...) calcs to build aggregates that work and avoid the added complexities of table calcs.

Jonathan

1 of 1 people found this helpful
• ###### 20. Re: Remove empty columns from year over year calculation

Thank you Yuri.

And this link is what I found as to retaining some property when moved to detail (You may already know...)

Looks like "hide" also works in similar way.

Notice when the DATE is on the DETAIL Shelf, there is not an OPTION to SHOW MISSING VALUES. So we are left with a VIZ that is not we we intended.

The way to get around this is simple. Just move the DATE field to the Column/Row Shelf, Check SHOW MISSING VALUES, and the DRAG it back to the Detail Shelf.

Now Drag it back

Tableau RETAINS the SHOW MISSING VALUES property when you move it back to the DETAIL SHELF!!!!!!

This is a nice trick to remember in the future!

Best regards,

Shin

• ###### 21. Re: Remove empty columns from year over year calculation

Hi Jonathan

Thank you for detail explanations.

I'm definitely interested in the detail and history. I will send you an email.

Thanks again!

Regards,

Shin

• ###### 22. Re: Remove empty columns from year over year calculation

Ok, Yuriy I have a new one for you.  Same concept, but the data is structured differently.  Basically I have one measure called Dollars that represents both Budget and Actual Dollars.  In order to ferret the correct measure out, I have to look at AccountType to see if it's Budget or Actual.  I won't go into too much unnecessary detail, but I've attached a workbook using data similar to my actual data.

I've only included 4 months of data and the Dollars are the same for each month in the sample.  You can see the Budgets are annual and Actual Dollars are monthly.

I've been working at this for a while, but I can't seem to figure out the right way to get rid of these two columns only:

Thanks a million for any help.

• ###### 23. Re: Remove empty columns from year over year calculation

To make LOD work, change the filter to context.

["Dalloar vs Budget"]

if [Table Name]="Sheet1" then [Dollar vs budget] end

["Dalloar vs Budget" delta]

if attr([Table Name])="Sheet11"

then zn(sum([Dollar vs budget]))-zn(lookup(sum([Dollar vs budget]),1)) end

["Dalloar vs Budget" delta %]

if attr([Table Name])="Sheet12"

then (zn(sum([Dollar vs budget]))-zn(lookup(sum([Dollar vs budget]),1)))/

zn(lookup(sum([Dollar vs budget]),1)) end

[Year Month LOD]

[DateTime]={MAX([DateTime])}

or [Table Name]={min([Table Name])}

And same procedure.

Put to [Year Month LOD] row, Hide false, and then put to detail.

Thanks,

Shin

2 of 2 people found this helpful
• ###### 24. Re: Remove empty columns from year over year calculation

Wow, thanks again Shin!  I'm definitely going to look into this method, but I do have one concern and that is duplicating a large data source 3 times to make this work.  The sample I attached is small of course, but the real dataset is much larger.  And also the complexity involved...I've really gotten myself into a rabbit hole here haha!

So, I will definitely give this a go if we think it can't be done without doing the duplication of data.  Really opening my eyes as to what Tableau can do though.  Very educating.

• ###### 25. Re: Remove empty columns from year over year calculation

I understand your concern, but without additional dimension, I cannot filter out specific measures.

If other filter can narrow down the original data in the datasource and also you can use extracted data, the size concern might not be the case.

Anyway,s it depends.

I don't have a solution so far without duplicating the data, though.

Regards,

Shin

• ###### 26. Re: Remove empty columns from year over year calculation

I might be able to make it work with the data duplication, but when you say "without an additional dimension" are there any dimensions that you can think of that would enable this to work without duplication?  I can always create new fields and/or manipulate the data..

Also, do you know why January is the only month that displays all 4 measures in your workbook?  Doesn't appear to work with the other months in the data.

• ###### 27. Re: Remove empty columns from year over year calculation

Without duplication, we can not get exact same slice of the data in same data set with any other dimensions..

Talking about filter, green is shown with this field's logic

[Year Month LOD]

[DateTime]={MAX([DateTime])}

or [Table Name]={min([Table Name])}

Thanks,

Shin

1 of 1 people found this helpful
1 2 Previous Next