tag:blogger.com,1999:blog-3998628784041768080.post4479078072001877420..comments2024-03-28T03:27:07.147+00:00Comments on VizWiz: Tableau Tip Tuesday: Using LODs to View the Latest, Previous and Prior MonthsVizWizhttp://www.blogger.com/profile/11408220384219218043noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-3998628784041768080.post-67236334577012666132018-04-23T20:29:10.357+01:002018-04-23T20:29:10.357+01:00Hi,
I discovered this and found easiest to unders...Hi,<br /><br />I discovered this and found easiest to understand for current month and last year same month: it works only for 1 month period<br /><br />current month : <br />if MONTH(TODAY())=MONTH([Date]) AND YEAR(TODAY())=YEAR([Date]) THEN ([Monthly Pack sales])<br />END<br /><br />prev year same month:<br /><br />if MONTH(TODAY())=MONTH([Date]) AND YEAR(TODAY())-1=YEAR([Date]) THEN ([Anonymoushttps://www.blogger.com/profile/05535492571633286492noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-64210614448029657982017-12-19T12:50:34.319+00:002017-12-19T12:50:34.319+00:00That would be just like the Last 3 months example ...That would be just like the Last 3 months example except change the number of months. VizWizhttps://www.blogger.com/profile/11408220384219218043noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-66802642717689740502017-12-06T06:57:11.302+00:002017-12-06T06:57:11.302+00:00Hi Andy,
What about finding the last 4 months fro...Hi Andy,<br /><br />What about finding the last 4 months from the data. We have two years of data and suppose current month is Feburary. We tried using Last() func in filter but the table level calc filter does not work on Grand Total.Anonymoushttps://www.blogger.com/profile/07876917133351237596noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-7082699839272092652017-08-05T20:59:44.979+01:002017-08-05T20:59:44.979+01:00If you're using FIXED LODs, then those will ig...If you're using FIXED LODs, then those will ignore your dimension filters unless you put them in context.VizWizhttps://www.blogger.com/profile/11408220384219218043noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-34292885356919749512017-08-05T11:28:26.660+01:002017-08-05T11:28:26.660+01:00Hi Andy,
Thanks for the video. It is amazing. I a...Hi Andy,<br /><br />Thanks for the video. It is amazing. I am trying to implement this. I want to show three columns, current month, previous month and same month previous year.I have a filter for selecting Month-Year. I am getting correct values for current month, however for previous month it shows 0. I think the calculations are not taking my filter selection into account.<br /><br />Is there Anonymoushttps://www.blogger.com/profile/15633205412010430139noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-58014954355474111842017-04-07T21:41:06.602+01:002017-04-07T21:41:06.602+01:00Hi Pj. You'll need to use the dayofyear functi...Hi Pj. You'll need to use the dayofyear function instead. More info here - http://onlinehelp.tableau.com/current/pro/desktop/en-us/functions_functions_date.htmlVizWizhttps://www.blogger.com/profile/11408220384219218043noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-79151481604709858392017-04-07T20:22:37.947+01:002017-04-07T20:22:37.947+01:00Hi,
Thanks for the video (https://www.youtube.com...Hi, <br />Thanks for the video (https://www.youtube.com/watch?v=ViD3vclgNv8)<br />I have been trying to get a similar calculation,<br /><br />I want to calculate year over year difference of Sales for all months. I used a table calculation but that will not be accurate for current month since it calculates Sum(Sales) for all days in current month in last year and only MTD for this year. Is there Anonymoushttps://www.blogger.com/profile/11777736212656084872noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-85780943247325517012017-03-19T17:27:31.106+00:002017-03-19T17:27:31.106+00:00Just less typing and it seems cleaner and easier t...Just less typing and it seems cleaner and easier to understand for me. VizWizhttps://www.blogger.com/profile/11408220384219218043noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-7749582870146775532017-03-19T17:19:01.626+00:002017-03-19T17:19:01.626+00:00Andy
I learned some good stuff in the post. Thanks...Andy<br />I learned some good stuff in the post. Thanks.<br /><br />Is there a reason in addition to fewer key strokes, that you used the non-scoped form of the LoD calc - {MAX([Order Date])} ? I understand that form as a shorter version of of the scoped form {FIXED : MAX([Order Date])} Does the non-scoped version run faster, or perhaps keep the character count down in the calc?BESegalhttps://www.blogger.com/profile/10584053046881482275noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-37249771167337184642017-03-15T14:44:58.322+00:002017-03-15T14:44:58.322+00:00Thank you for your reply, Andy.
I will try it.
Ch...Thank you for your reply, Andy.<br />I will try it.<br /><br />Cheers<br />HolgerAnonymoushttps://www.blogger.com/profile/11221815478156900099noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-21101130204292546542017-03-15T11:23:14.349+00:002017-03-15T11:23:14.349+00:00Holger, in this case, each IF statement would need...Holger, in this case, each IF statement would need to also have ELSE 0 included. Then for the same month prior year, you simply duplicate the calc and change offset the months by 12.<br /><br />For example, I would change Latest Month Sales to:<br />IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=0<br />THEN [Sales] <br />ELSE 0<VizWizhttps://www.blogger.com/profile/11408220384219218043noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-27425769263130482512017-03-14T20:58:11.176+00:002017-03-14T20:58:11.176+00:00Hi Andy,
thank's for your post.
Could please ...Hi Andy, <br />thank's for your post.<br />Could please give me a hint how to something like last 3 month versus the corresponding three month a year ago?<br />(for example Dez'16/Jan'17/Feb'17 vs Dez'15/Jan'16/Feb'16)<br />Any help would be great.<br /><br />Thank you<br />Cheers<br />HolgerAnonymoushttps://www.blogger.com/profile/11221815478156900099noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-26096503183514659162017-03-14T16:39:23.330+00:002017-03-14T16:39:23.330+00:00Yeah I'd probably do that too. Good tip!Yeah I'd probably do that too. Good tip!VizWizhttps://www.blogger.com/profile/11408220384219218043noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-21214331504348882532017-03-14T16:38:35.683+00:002017-03-14T16:38:35.683+00:00Ah nice one Jonathan! You can probably tell the lo...Ah nice one Jonathan! You can probably tell the logic that went through my head. Thanks for the improvement!! I'm going to test it in sql as well. VizWizhttps://www.blogger.com/profile/11408220384219218043noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-57005971513863141412017-03-14T16:25:01.730+00:002017-03-14T16:25:01.730+00:00Andy,
Agree, great post and great improvement fro...Andy,<br /><br />Agree, great post and great improvement from Jonathan.<br /><br />When I've implemented this I will usually have a date selector parameter in case we don't want the latest date to be the reference data. For example, let's say the end of the month occurs on a Saturday and we come into work on a Monday and were never able to see the full month compared with the Anonymoushttps://www.blogger.com/profile/05368154405394680538noreply@blogger.comtag:blogger.com,1999:blog-3998628784041768080.post-16589071680455117712017-03-14T15:56:23.455+00:002017-03-14T15:56:23.455+00:00Hey Andy,
Nice tip! For performance the DATETRUNC...Hey Andy,<br /><br />Nice tip! For performance the DATETRUNC() should go outside the MAX(), here's why: Let's say there are 1M rows. With {MAX(DATETRUNC('month',date))} the DATETRUNC is computed 1M times, then the MAX is computed. Whereas with {DATETRUNC('month',MAX(date))} the MAX is computed once and the DATETRUNC is computed once.<br /><br />Cheers,<br /><br />Jonathan<Anonymoushttps://www.blogger.com/profile/07246437048304077347noreply@blogger.com