tag:blogger.com,1999:blog-8663284849781850746.post2480264416479436832..comments2023-10-31T05:11:15.693-07:00Comments on Paint By Numbers: Tableau Tip - The End of Time.... series based calculationsPeterGhttp://www.blogger.com/profile/00017874900847973883noreply@blogger.comBlogger20125tag:blogger.com,1999:blog-8663284849781850746.post-25172529131289502282016-07-15T12:46:13.456-07:002016-07-15T12:46:13.456-07:00Hi! is there a way to show last 12 month only in a...Hi! is there a way to show last 12 month only in a moving 24 month dara base?<br />Thanks!Ariela Bitranhttps://www.blogger.com/profile/07275377112019773139noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-58871795585000254932016-05-27T01:28:09.001-07:002016-05-27T01:28:09.001-07:00teechip
fabrily
sunfrog coupon
teezily
snorgtees...<a href="http://teechip.info/" rel="nofollow" title="teechip">teechip</a> <br /><a href="http://fabrily.info/" rel="nofollow" title="fabrily">fabrily</a> <br /><a href="http://thepetshirts.com/" rel="nofollow" title="sunfrog coupon">sunfrog coupon</a><br /><a href="http://teezily.info/" rel="nofollow" title="teezily">teezily</a><br /><a href="http://snorgtees.info/" rel="nofollow" title="snorgtees">snorgtees</a><br /><a href="http://redbubbles.org/" rel="nofollow" title="redbubbles">redbubbles</a><br /><a href="http://funnydogtshirts.com/" title="funny dog tshirts" rel="nofollow">funny dog tshirts</a><br /><a href="http://dognightshirts.com/" title="dog night shirts" rel="nofollow">dog night shirts</a><br /><a href="http://dogsportshirts.com/" title="dog sport shirts" rel="nofollow">dog sport shirts</a><br /><a href="http://bonfirefunds.info/" rel="nofollow" title="bonfire funds">bonfire funds</a><br /><a href="http://shirtmagic.info/" rel="nofollow" title="shirtmagic">shirtmagic</a><br /><a href="http://merchify.info/" rel="nofollow" title="merchify">merchify</a><br /><a href="http://design4t.info/" rel="nofollow" title="design4t">design4t</a><br /><a href="http://viralstyle.info/" rel="nofollow" title="viralstyle">viralstyle</a><br /><a href="http://sellmytees.info/" rel="nofollow" title="sell my tees">sell my tees</a><br /><a href="http://custom-ink.net/" rel="nofollow" title="custom ink">custom ink coupon</a><br /><a href="http://ooshirt.info/" rel="nofollow" title="ooshirts">ooshirts coupon</a><br /><a href="http://vistaprints.info/" rel="nofollow" title="vistaprint">vistaprint</a><br /><a href="http://bigfrog.info/" rel="nofollow" title="bigfrog">bigfrog</a> <br /><a href="http://5shirts.info/" rel="nofollow" title="5 shirts">5 shirts</a><br /><a href="http://design-a-shirt.info/" rel="nofollow" title="designashirt">designashirt</a><br /><a href="http://shirt-city.info/" rel="nofollow" title="shirtcity">shirtcity</a><br /><a href="http://theprintful.info/" rel="nofollow" title="theprintful">theprintful</a><br /><a href="http://threadmeup.info/" rel="nofollow" title="threadmeup">threadmeup</a><br /><a href="http://unionshirts.info/" rel="nofollow" title="unionshirts">unionshirts</a><br /><a href="http://gearlaunch.info/" rel="nofollow" title="gearlaunch">gearlaunch</a><br /><a href="http://teespring.asia/" rel="nofollow" title="teespring">teespring</a> <br /><a href="http://sunfrogshirts.info/" rel="nofollow" title="sunfrogshirts">sunfrogshirts</a> <br /><a href="http://www.badideatshirts.info/" rel="nofollow" title="badideatshirts">badideatshirts</a> <br /><a href="http://mostexpensivedog.info/" rel="nofollow" title="mostexpensivedog">mostexpensivedog</a><br /><a href="http://dragonee.club/" rel="nofollow" title="dragonee">dragonee</a> <br /><a href="http://viralstyle.club/" rel="nofollow" title="viralstyle">viralstyle</a><br /><a href="http://teezily.club/" rel="nofollow" title="teezily">teezily</a><br /><a href="http://ooshirts.club/" rel="nofollow" title="ooshirts">ooshirts</a> <br /><a href="http://gearbubble.club/" rel="nofollow" title="gearbubble">gearbubble</a> <br /><a href="http://skreened.club/" rel="nofollow" title="skreened">skreened</a> Flagwix Inchttps://www.blogger.com/profile/03932840404451670588noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-66315684407743957752015-06-29T09:51:17.952-07:002015-06-29T09:51:17.952-07:00I've actually been working on a blog post on t...I've actually been working on a blog post on this very topic, I'll have it up later this week and will link to it.Anonymoushttps://www.blogger.com/profile/07246437048304077347noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-5201915149796416962015-06-29T07:02:00.840-07:002015-06-29T07:02:00.840-07:00If you could include detailed step by step instruc...If you could include detailed step by step instructions, I would greatly appreciate it. Thank you in advance for your assistance.Jeremy Ehttps://www.blogger.com/profile/07788695590000174986noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-26777720607145456652015-06-29T07:01:08.255-07:002015-06-29T07:01:08.255-07:00May I ask if it would be possible to get a detaile...May I ask if it would be possible to get a detailed explanation of applying this principle to a different type of data?<br /><br />For example, I would like to see the US Sales totals, and have the ability to filter it to a US state without the ability to select a US territory (Guam, Puerto Rico, etc), but to have the US territory sales remain in the US national totals. How could I do this?Jeremy Ehttps://www.blogger.com/profile/07788695590000174986noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-32012173046309702832015-03-17T08:01:14.102-07:002015-03-17T08:01:14.102-07:00Hi PeterG/Jonathan,
Thanks for post and comments ...Hi PeterG/Jonathan,<br /><br />Thanks for post and comments lot of leaning.<br /><br />I have quick a question i want to give date field in filter and when ever user select the month we should be able to view month value for selected month and as well as YTD for selected. I tried doing this but when filter the month table calculation on YTD is lost and end up with selected month value<br /><br />Thanks in Advance for any help.<br /><br />Regards<br />Bhujang Anonymoushttps://www.blogger.com/profile/03373194831731916841noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-64144413729188338402014-07-29T08:17:47.627-07:002014-07-29T08:17:47.627-07:00Joe,
If you post something to the Tableau forums ...Joe,<br /><br />If you post something to the Tableau forums and ping me I'll talk a look, I'm quite sure we can get what you need.Anonymoushttps://www.blogger.com/profile/07246437048304077347noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-64833271509657986352014-07-29T08:17:00.528-07:002014-07-29T08:17:00.528-07:00Hi,
Hi, I'm just getting to this thread...her...Hi,<br /><br />Hi, I'm just getting to this thread...here's a link to the KB article on relative date filters: http://downloads.tableausoftware.com/quickstart/feature-guides/relative_dates.pdf.<br /><br />And here's a quick implentation of a relative date filter combined with a LAST() filter on Tableau Public: https://public.tableausoftware.com/views/relativedateandlastfilter/AddLastFilter.<br /><br />The steps are:<br /><br />1) Set up your worksheet.<br />2) Add the relative date filter on your date dimension, using the KB article. Validate that the results of the worksheet that you want to display are still unchanged.<br />3) Create a calculated field with the formula LAST()==0.<br />4) Drag that field onto the Rows Shelf, and filter for True. Assuming that your date dimension and your date dimension is the only dimension on Columns, Tableau's default Compute Using will work for you.***<br />5) Validate that the result for that last date is correct, then drag the pill from Rows to the Filters Shelf, and Filter for True.<br /><br />*** If your view has a different arrangement of dimensions, then the Compute Using of the LAST()==0 pill will need to be set differently and exactly what that will be is dependent on that arrangement. You can experiment to find out what might work, or you can get more help by posting a packaged workbook with some sample data to the Tableau community http://community.tableausoftware.com/ or Tableau Talk http://tableautalk.com/.<br /><br />Cheers,<br /><br />Jonathan<br />Anonymoushttps://www.blogger.com/profile/07246437048304077347noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-8408036740822328532014-07-29T06:37:21.499-07:002014-07-29T06:37:21.499-07:00Hi Ryan, I'm really pleased to hear that this ...Hi Ryan, I'm really pleased to hear that this helped you, and your 90 day tweak sounds like a nice twist on the idea. On point 2, I suspect this may be to do with where the index dimension pill was placed in relation to the other dimension pills on the shelf (i.e. before or after). But anyway, detail did the trick so all good!PeterGhttps://www.blogger.com/profile/00017874900847973883noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-81474669589300937272014-07-28T15:24:05.829-07:002014-07-28T15:24:05.829-07:00This was great. I used for a running sum viz of n...This was great. I used for a running sum viz of new customers by day.<br /><br />Two comments:<br />1) I was able to get a trailing 90 days with the slight index match formula tweal of [index]>=window_max([index])-90. And the 90 value here can be turned in to parameter.<br />2) When I added index and index match pills to the worksheet, my viz was altered in a bad way. Luckily, I get the same desired filtering effect by just adding them to the DETAIL shelfAnonymoushttps://www.blogger.com/profile/18055562748819812733noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-26247827401783927612014-07-14T04:00:11.867-07:002014-07-14T04:00:11.867-07:00This comment has been removed by a blog administrator.21st Century Software Solutionshttps://www.blogger.com/profile/03192223264030252287noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-38936674780121282662014-06-13T14:06:57.837-07:002014-06-13T14:06:57.837-07:00Would it be possible for you do walk through the n...Would it be possible for you do walk through the necessary actions using the Last()==0 method like Peter has done to show how it works?<br /><br />I am a new user and would like to try this out.<br /><br />I am basically in this exact situation… “…I might have a data set that is updated monthly with three years of data in it, but have a view that shows month over month changes for the past 12 months. I'll set up a Relative Date filter for the last 14 months” Help me12https://www.blogger.com/profile/03716914330177534099noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-89773440942253120312014-06-09T08:44:52.422-07:002014-06-09T08:44:52.422-07:00This technique is SO CLOSE to what I need to do! ...This technique is SO CLOSE to what I need to do! I know my last day of data in my data source, and I have a billion table calcs going on. One line (on a multiple-line-graph viz) is historical data, and it contains a full year's worth of data whereas current year's data runs to some date that is less than today(). (Oh, would that make things easier for me if it were always current!) If I try adding one more element to filtering such that I try to chop off historical data beyond this-year's last-date in the history's range, the calc to sum up the history data doesn't function properly, just as Jonathan noted might happen.<br /><br />If there was a way to use LOOKUP to display only data less-or-equal to the maxDate calc I create, I'd be there.<br /><br />Anonymoushttps://www.blogger.com/profile/12890420057584153879noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-66633891226137093972014-06-03T02:40:13.774-07:002014-06-03T02:40:13.774-07:00Really useful post Pete and great comments as ever...Really useful post Pete and great comments as ever Jonathan. I flipped the LAST()==0 to hide incomplete months by adding it to filters and it will hide incomplete months. Again, there is probably an easier way to find this but really quick and simple method to stop a line chart of volumes over time not showing a massive dip when only part way through a month or week.Carl Allchinhttps://www.blogger.com/profile/18385923109850401921noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-69245814933443284442014-02-18T07:57:58.544-08:002014-02-18T07:57:58.544-08:00Very helpful! I have a data set by date and only a...Very helpful! I have a data set by date and only am looking for the end of the series - perfect timing!Stacey Rothchildhttps://www.blogger.com/profile/06260557546326252396noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-74054885218041273062014-02-17T11:35:12.198-08:002014-02-17T11:35:12.198-08:00thanks Jonathan, that was a seriously helpful answ...thanks Jonathan, that was a seriously helpful answer and is going to come in useful for loads of things, and I really appreciate it! I am really looking forward to yours and Joe's book to help get to the bottom of things like this. Unfortunately I still come across things where I just have to use trial and error to get it to work as certain actions can seem inconsistent.<br /><br /> I guess one of the disadvantages of Tableau's user interface is that it can be so easy on the surface you don't (or at least I don't!) think about things in the same structured and ordered way as I would writing SQL. This deeper level of understanding is definitely where I need to go next to become more efficient.PeterGhttps://www.blogger.com/profile/00017874900847973883noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-63060846099104056772014-02-17T11:12:28.727-08:002014-02-17T11:12:28.727-08:00The short answer is that not all filters are appli...The short answer is that not all filters are applied at the same time, so sometimes filtering will muck up table calcs and sometimes it won't, depending on what kind of filter you are using. Here's a rough order of operations:<br /><br />Drag a pill or do something to trigger a view update<br />…Tableau builds the query(ies) to the data source(s), trying to do as much of the computation there as possible…<br />Apply data source filters (added to the WHERE clause of every query in SQL data sources)<br />Apply context filters (creates a temp table with the data needed)<br />Apply Top & conditional filters on discrete dimensions (includes Top & Conditional Sets) (uses a sub-query that is joined to the results)<br />Apply regular filters on dimensions, including general & wildcard filters for discrete dimensions & Sets (& Filter Actions), and range filters on continuous dimensions. (WHERE clause)<br />Calculating results for calculated dimensions & aggregate measures, including computation of aggregate measures & calculated fields that don’t depend on data blends<br />Apply filters on aggregates (that don’t depend on blends) (HAVING clause)<br />...data is returned to Tableau...<br />Data blending, including lining up the domains of the linking dimension(s) which can have a "filtering" effect on the data.<br />Densification, including domain padding and domain completion<br />Computation of calculated fields that do depend on data blends<br />Apply filters using calculated fields that do depend on data blends<br />Computation of table calculations & R scripts<br />Applying filters based on table calcs & R scripts<br />(in a separate pipeline, computation of grand totals, including table calcs inside grand totals)<br />(in yet another separate pipeline, forecasting)<br />…lots more stuff for mapping, Hiding data, view layout, z-ordering of the displayed elements…<br />Displaying the view<br /><br />[As of last count, there are over 100 separate steps in the Tableau computation & rendering pipeline. I've explored this pretty deeply for the book Joe and I are writing, and I'm amazed at how much Tableau can do in that split-second between us dropping a pill and the view being rendered).<br /><br />Using an IF statement like the one you described is a row-level calculation that will be computed in the data source, and therefore Tableau won't have the needed data to compute a result, and it fails. On the other hand, the table calculation filter is applied *after* the table calcs have been computed, so it returns an accurate result. For this reason, some folks call table calc filters "late" filters, because they are later in the pipeline. Does that make sense?<br /><br />With this knowledge, another alternative to have a fixed end date for filtering would be to do something like LOOKUP(DATETRUNC('month',MIN([Order Date])),0) == #2013-12-01#, where the date could be replaced with a parameter. LOOKUP() is a great calc for wrapping other results in a table calculation and then using those in a filter, one other nice feature of LOOKUP() is that Tableau will let us filter table calc discretes (table calc blue pills), while it doesn't let us filter on regular aggregate discretes (regular blue pills). The potential downside is that as a table calculation, LOOKUP() requires an aggregate or constant value as an argument and therefore is dependent on having the right level of granularity in the view.<br /><br />A couple of other useful posts on this general topic are: <br /><br />http://community.tableausoftware.com/message/139603#139603 - Joe's original post on the order of operations<br />http://www.theinformationlab.co.uk/2013/08/12/cascading-quick-filters-in-tableau/ - A fantastic post by Andrew Ball about nesting filters<br /><br />Jonathan<br />Anonymoushttps://www.blogger.com/profile/07246437048304077347noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-22252924909576108052014-02-17T06:30:05.781-08:002014-02-17T06:30:05.781-08:00oh and filtering on the index match on my example ...oh and filtering on the index match on my example also works. It confuses me why that keeps the calc in tact but if I used a filter like "if month([Order Date])=12 and year ([Order Date])=2013 then 1 else 0" end it would not.PeterGhttps://www.blogger.com/profile/00017874900847973883noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-49952083855051014602014-02-17T06:27:56.820-08:002014-02-17T06:27:56.820-08:00Hi Jonathan, thank you for the comment. I really l...Hi Jonathan, thank you for the comment. I really love seeing the different ways in which people have worked around problems, and your method requires one less step which is definitely useful! By the way to make mine flexible to choose other end dates I use separate date filter.<br /><br />I have a question for you about filters. The reason I used Hide was my (wrong) assumption that using a filter would muck up the table calcs, the same way that filtering on date would, or any other filter for that matter. So my question is, if LAST()==0 is FALSE for all but one date, and you filter on LAST()==0 =TRUE then how are the other date values still around to run the table calculations running sum, difference etc....?PeterGhttps://www.blogger.com/profile/00017874900847973883noreply@blogger.comtag:blogger.com,1999:blog-8663284849781850746.post-70936163776202085732014-02-17T06:06:46.856-08:002014-02-17T06:06:46.856-08:00Hi Peter,
There are definitely multiple ways to g...Hi Peter,<br /><br />There are definitely multiple ways to go about this, here's the one that I use: A table calc with the formula LAST()==0, the Compute Using is set the Date, and then put on the Filters Shelf to filter for True.<br /><br />FIRST(), LAST(), and SIZE() are really useful table calcs. FIRST() provides the offest to the first address (aka row) in the partition, LAST() provides the offset to the last address in the partition, and SIZE() returns the number of addresses in the partition. LAST() returns 0 for the last address in the partition, and with Tableau's default date sort being ascending order, the last address will be the latest date, so LAST()==0 will only return True for the latest date. <br /><br />I use the Filters Shelf rather than Hide to make the viz more discoverable to other Tableau Desktop users. There's no visual indicator that anything's been hidden, but we can always see the pills on the Filters Shelf and interrogate them.<br /><br />Also, for views like this another thing that I do is set up a Relative Date filter on the date dimension in the view, to pull up as many dates as I need and a little bit more for insurance, and more importantly to *not* pull up dates that I don't need. This is because table calc filters like LAST()==0 are applied after the queries have been made to the data source, and Tableau is faster when it's not retrieving data we don't need. For example, I might have a data set that is updated monthly with three years of data in it, but have a view that shows month over month changes for the past 12 months. I'll set up a Relative Date filter for the last 14 months, 12+1 to make sure the month over month calculations are accurate, and an extra month just in case the data load is a bit late. This filter is processed as part of a WHERE clause in the data source, and then the LAST()==0 filter is only applied to the reduced data set.<br /><br />Jonathan<br />Anonymoushttps://www.blogger.com/profile/07246437048304077347noreply@blogger.com