Issue#31: Tableau - Creating a table calculation that sets goals to be 50% higher than the previous high value in data set

  • Calling all Tableau Experts!


    I have 10 months of data, January thru October. I’m trying to write a table calculation that sets a new goal for First Pass Yield that’s 50% higher than the previous high First Pass Yield monthly aggregate. That’s a bit of a mouthful, so here’s an example to better illustrate what I’m looking for (Reference the below table for the values I’m talking about):




    First, obviously, January has no previous lookup value to measure against so I’m setting that Goal value to just be .5 (just so there are no Null values in the table calc).


    For February’s goal, the calculation should be January’s FPY actual plus 50%, which in this case would be 77.27%.


         Goal % Calc = ((1-((Lookup(Previous High Value)))*.5)+(Lookup([Previous High Value)))


    Now, because February’s FPY was lower than the first month, the goal would still be set at 77.27%. This trend continues until we get to June where we see our first FPY value that surpasses our first FPY max. Therefore, the goal for July should be 50% higher than the newest FPY high of 72.97%, giving us a new goal of 86.49%. Lastly we see yet one more increase to the monthly FPY in August, which bumps up the new goal to 87.06%.


    Now, it would be pretty simple to just find the window_max of FPY, add 50% and make that goal for all months, but I want to maintain the integrity of the goal values for the time frames they were applicable. (This way I can keep and track the history of our goal progressions)


    Does anyone have any suggestions how to write a table calc (or multiple table calcs) to accomplish this task? I’ve been trying to use Lookups, Window_Maxs, etc. in multilple calculations but I’m just not able to get all the way there.


    Attached is a Packaged workbook using the above table to show what the desired results for the goal bars *should* be.


    Please help!!

    0 Answers

    Sorry, your answer is too short !

  • Issue#675876: Tableau - Calculation Issue: Joining Two Data Sets for Calcs

    Hi all -- Can someone please help me with this issue I've been having? I'm currently trying to join two different data sets together, but can't find a good way to do it. Please see below for what my two tables look like: In one table, I have a date and a return In another table, I have a date and expected average return for that date: Ideally, I'm just trying to calculate from the first table, what the Return - Expected Avg Return is for each date, so in theory it would look something like this: Then, from there, I would just do Return - Avg Expected Return, and have an average of that number for each date. I keep running into the issue that I can't mix Aggregate and Non-Aggregate values -- anyone know an easy solution?

  • Issue#58696: Tableau - Need Help in my Table Calculation when Blending the data

    Hey Tableau Experts, I am facing a huge problem when I am blending the 2 data sources. I am creating a dashboard where I need to provide the amount in 1 currency using a 2nd data source which will provide me the currency conversion data on a particular day.Issue is, amount is converting correctly on row level but when I am applying the total in the view or taking that converted calculated field it is not showing me the desired results. I am attaching a TWBX with sample data similar to the actual data. Please let me know if you need any me my work is totally stuck. Regards,Nakul

  • Issue#46: Tableau - Table Calculation with LOD expression and IF statement

    Hello Tableau Community, I am struggling with a table calculation using LOD expression and an IF statement. Below is my data-set, I would like to calculate the MAX of each SOW for a specific date range (between 01/31/2015 and 03/31/2015).  The formula { FIXED [Sow Id]: MAX([SOW Value])} is working perfectly until I add the condition to it. See the screen below...  If somebody has any hint please let me know. PS: I do not want to use filters because I will not be using only one measure. Thank you very much in advance,Cheers, Maxime

  • Issue#50: Tableau - Tableau creating wrong syntaxs when accessing data through ODBC connection

    Hello , We are accessing data from one of our hive cluster using quboles ODBC connector and for some reason Tableau is not able to recognize the data types ,So we did change the data types from the tableau interface and tableau is trying to send a query with a wrong syntax to the hive server . So here is an example of what I am talking about set hive.cli.print.header=false;explain SELECT 1 AS `column1`, `Tablename`.`column2` AS `xyz`, `Tablename`.`ad` AS `ad_slot`, `Tablename`.`advertisername` AS `advertisername`, CAST(CAST(`Tablename`.`cpm` AS `DOUBLE`) AS `DOUBLE`) AS `cpm`FROM `db`.`Tablename` AS `Tablename` LIMIT 10000  Here in the above sample query you can see in the highlighted part where Tableau is keeping the data type in the quotes and the underlying data base engines(Hive) are unable to understand the syntax and thus leading queries to fail. So the question is how do we get around this problem of the syntax that tableau is sending to the Hive se

  • Issue#82: Tableau - Put the table/view name in the View Data window when previewing the contents in a Data Source.

    When previewing the data in a table/view in the data pane of a Data Source it would be helpful to have the name of the object being viewed in the title bar. Preview the table/view: ...and in the title bar put the name of the table/view that is being displayed: When looking at multiple screens of custom SQL queries and bouncing between table/view previews I will sometimes draw a blank as to what table/view I'm previewing.  This is particularly helpful when I get pulled away and return to what I was doing as I would know right away what it was I was looking at before the interruption.

  • Issue#24221: Tableau - Distinct count of Data as on end of a particular month (including previous months).

    Hi, Could you please help me to find out a calculation, which will summarize the distinct count of Part numbers as on end of a particular month (including previous months). My data something looks like the below; PartDateP-1231-Jan-15P-2313-Jan-15P-43215-Jan-15P-1231-Feb-15P-45114-Feb-15P-25322-Feb-15P-2311-Mar-15P-46110-Mar-15 I am looking for a summary table like; MonthDistinct count of part No as on monthDistinct Count :consolidated with previous month dataRunning totalJan-15333Feb-15325Mar-15216 Here Jan-15 total distinct count of  part numbers are 3. In Feb-15 there are 3 distinct part numbers, but consolidating the previous month also in calculation it is only 2 (Part number P-123 already accounted in Jan-15). Thanks in advance. Aneesh

  • Issue#34505: Tableau - Creating trend line with long form data

    Hi guys, *this might be a super simple fix but my Tableau skills are...lacking So I have data with the following columns: Date, Athlete, Factor, ValueAnd as variables for the "factor" column, there are things like Sleep Hours, Soreness, Stress, etc. What I'm trying to do is build trend lines between things like Sleep Hours and Stress over time - and looking at how these things correlate.  I have measure built for each variable under factors: IF [Factor] = 'Stress' then [Value] ENDBut when I grab two of these measures, stress and sleep hours, the graph returns that I have 250 nulls or however many. I'm assuming this is because when Tableau searches each row it looks for Stress and finds, ok stress = 5, but then in that same row, there is no corresponding value for sleep hours - because of course that value is contained in its own row of data with the factor, "sleep hours" and the corresponding value. These values are of course only linked by the name of the athlete and th

  • Issue#60590: Tableau - Table name changed after extracting data - Error - Invalid table name valueT

    Hello! I'm getting below error as the name of a table I'm using in custom SQL has changed (after I extracted data into the workbook). I'm using Tableau Desktop 9.2. When I click on Data Source, I get prompted to enter connection details (Cloudera Hadoop). When I hit OK, then I get above message. However, the screen where I set up custom SQL is not coming up. How do I modify my sql to reflect new table name and resolve above error? Thanks for your attention! Ravi.

  • Issue#69400: Tableau - Read previous month data

    Hi All, I am using oracle custom sql  and tableau, every month we  run this report and we find out every month we are missing some data , so we want to comapre  3 month daTA , this month, previuos month and previous 2 month. How do i do it? My IDea is  1) Creat a extract such that it read previous month and previous 2 month automatcially. (Dont know how to do it)2) Scehdule  oracle data to excel and read it.(But I am not DBA donno how to do it...) Please guide me   how to do it  or  any other  way as u think. Tableau User@ ThANKS

  • Issue#70832: Tableau - Grouping Data in sets of 5

    I have 2 columns, UserID and RecordID The RecordID is unique and there is one record every time the user logs intio my app.  How can I make groups like this:How many users logged in between 1 to 5 times, 6 to 10 times, 11 to 15 times, etc... Thanks

  • Issue#93695: Tableau - Table calculation using rank

    Hi, I'm trying to translate some logic I have in SQL to Tableau. My schema is like this (table header and two rows): customer_id, date, A1, 2015-01-01, 51, 2016-01-02, 6 I want to calculate the value of A, for the first date of each customer. In SQL, I would do something like this:SELECTA,RANK() OVER (PARTITION BY customer_id ORDER BY date) AS rank....QUALIFY rank=1 In Tableau, I see there's a rank function, and I can specify to partition over customer_id.To look at just the first rank, would I just create a calculated field that says 'IF rank=1 THEN 1 ELSE 0 END' and filter on that being = to 1? thanks!

  • Issue#94929: Autodesk - Help creating a data extraction table without wizard

    Ok so here goes. I have a number of blocks created and added to my tool pallette. At the end of a drawing I would like to have a table added which counts all the blocks and lists them in the table. Now i can get this working using the data extraction wizard but I want to make it a one button push command. So at the end of the drawing the user would click on the button then click on the screen to display the table. This table would auto update or show an x through it o show that it needs updated. I have seen it done on bolt on software so assume its possible within acad itself. Would this be better to be performed using a lisp routine? If so could anyone point me in the direction of where to pick one up? Ive not done any Lisp since leaving college.

  • Issue#110359: Tableau - Table Calculation

    Hi Guys, I need your suggestion on how to show the drop or gain in data when compared to previous day. I am using table calculation to get the difference from previous day but on the chart the data seems to be confusing. I am looking for something like a view below - If there is a drop or gain it needs to be highlighted with red and green for individual day. State      Feb 12       Feb 13     Feb 14 CA           30                20          40 NY           40                30          10 Thanks for your help,JC

  • Issue#120055: Tableau - creating filtered and unfiltered components of a view through data duplication/ relationsh

    Hello,I am attempting to create a dual axis chart with a filtered and unfiltered component through the method suggested my michel cassie in the following thread: Can i have a Dual axis chart but with a filter applied only on one?  In the attached example workbook, I have attempted to remove the department relationship between the original and duplicate data sets in order to enable filtering the bars (original data) by department while leaving the line representing moving average of profits of all departments (duplicate data) unfiltered. However, filtering still affects the moving average.  Would anyone be able to take a look and offer any suggestions as to why I have not succeeded in producing a moving average line that is not affected by a department filter? Many thanks,Zach

  • Issue#132732: Autodesk - Creating a point table using survey data

    How to create point table using;1) coordinates of the center line is available 2) Suvey data for cross section available as follows; 

  • Issue#149741: Tableau - Vlookup value in a second data source and use in calculation

    This is a dashboard for comparing one company performance against the whole industry. I have a parameter field where dashboard user enters a name of a companyAs a separate data source I have a table :Company nameIndustryCompany 1 Auto......Company NCPG Is it possible to look up the industry value for the company in the second data source and based on that value choose what industry should it be compared against?So ideally the calculation would look something like this:look up to what industry this company belongscalculate industry performance  in pseudo code :SUM ( IF industry = vlookup(company in company-industry table) then VALUE END )

  • Issue#174951: Tableau - Rank calculation issue

    Hello Friends,Quick question here- On the attached, I'm trying to display the top N customers based off my parameter "Top Customers 1". Everything seems to work correctly until I begin to filter my view. For instance, if I select "STS" instead of "All" on the Business Segment quick filter, Tableau doesn't display the specified number of customers based off my Top Customers 1 parameter. Any idea how to resolve? Regards,Mikey

  • Issue#199315: Tableau - Filtering in on Specific Dates on Data Set and Creating buttons on Dashboard

    Hi All, I am creating a dashboard and looking to create a function on my dashboard which allows me to click on a button (eg. Out of date, going out of date next week, out of date in the next 4 weeks.) The purpose of the dashboard is to show all data and then the user is able to filter in on their region and as mentioned above the data which is out of date. Is there a way to create a parameter or calculated field to: 1.) Look at specific date ranges (needs to be by Day Month and Year) by pulling across a calculated filed or parameter2.) Then on the dashboard create buttons which selects the out of date/going out of date etc.3.) This needs to work with other filters created. Thanks in advance! Andy

  • Issue#218229: Tableau - Date filter created with table calculation applying to multiple sheets

    I have created a dashboard with two worksheets (two maps, as in USA/Alaska) and three filters. I want those three filters to action on both sheets. I can do it with two of them with the Apply to worksheets.. option. But for the third one, as I used it for years to get difference between values, I created it with a calculated field LOOKUP(ATTR(YEAR([Date Order])),0). So there is no Apply to worksheets option. So the USA map applied year is selected with that filter, but Alaska remains in the first one. Any workaround?  Thank you so much.

  • Issue#246313: Tableau - How to look only at the last row of data to populate a table/data sheet?

    Hi! I have an excel spreadsheet with various cash values over a period of time. I'd like to create a dashboard that highlights the most recent numbers, and I want this dashboard to update automatically as we add additional metrics each month. Another challenge, I want to create 2 separate columns of related date. For example, this is what I'd like my dashboard to show: Last Month's MetricsBudgetedActualTotal Cash ReceiptsTotal Cash Receipts (A+B)Cash Receipts ACash Receipts BCash Receipts C Is there a calculation I can create on the Month-Year measure to use that as a trigger so the dashboard only pulls from that row? Or any other suggestions? Here is a sample data set:  Month-YearTotal Cash ReceiptsTotal Cash ReceiptsBudgetTotal Cash Receipts (A+B)Total Cash Receipts (A+B) BudgetCash Receipts ACash Receipts A BudgetCash Receipts BCash Receipts B BudgetCash Receipts CCash Receipts C Budget2015-09100000010000009000009000008000008000001000001000001000001000002015-1011

  • Issue#256853: Tableau - Calculation for showing data of this current week

    Hello, I am still learning on Tableau and am currently needing help with a calculation. I am creating a report that will show me real time data for the current week.  Attached is an excel spreadsheet of the data I am trying to filter through. My formula is showing me the Scheduled Starts and should count all rows with A Status and rows with START Status that have a Start Date of today. However, I am wanting to change the TODAY() to somehow count rows with START Status that have a Start date of this current week, which is Sunday through Saturday (3/6/16-3/11/16).  The ideal calculation would be something like this:IF ([Status] = 'START' AND [Start Date] = THIS WEEK()) THEN 1ELSEIF ([Status] = 'A') THEN 1ELSE 0END And the total count would give me 19 records instead of 17.  I attached a workbook that has the calculated formula of Scheduled Starts (This Week) in case that helps.

  • Issue#257856: Tableau - Data blending Zip codes performance issue

    I am looking for the best / most efficient way to blend or join a secondary data source which contains a master zip to territory mapping. Primary data source: (live connection tableau online) each row has a zipSecondary data source: (excel) master zip file with ~40k rows (all US zips) - each zip is tied to a territory I am looking to add the territories from the master zip to territory file as a filter. I tried data blending using the zip codes as a common field but I am facing very slow performance. Is there a better way to do this?

  • Issue#271627: Tableau - Average calculation based on period with months with no data

    The dates in my spreadsheet are anywhere from October 2014 to February 2016 (range of 17 months).   There is no data for July and August 2015.  When I build my bar chart graph, July and August 2015 are showing in it but Tableau calculate the average based on the months that have data (so 15 months instead of 17).  If I did 82 / 17, my average would be 4.8, which is what my clients wants to see.  Is there a way to work around this?  Graph is attached.

  • Issue#276716: Tableau - Data Blending Left Join Issue

    Hi All, We are currently developing a customized usage dashboard for our business users. We are blending data using multiple data sources and both data sources are connecting to PostgreSQL databases. In the second data source we are using custom SQL which basically pulls the users who are in Usage and Admin AD groups. All sites contains corresponding Usage and Admin AD groups accordingly. Our aim is to provide the users logged in with their sites usage data. For example, user X is only part of site A and when he logs in he should be viewing only site A's usage statistics and if user Y is part of site B and C, he should be viewing both sites B and C's usage statistics. For the security piece we are capturing user login information using GetUserName() function. But we have a issue here with the Site Name filter. When a user X logs in, he is able to see only his sites corresponding data getting filtered but the site name filter shows all sites within the environment. we want to

  • Issue#329070: Tableau - Calc that uses Table row in calculation (Retention Calc)

    Hi all, I have a tricky retention report I'm trying to create to no avail. I have a data source that tracks orders by member, listing each member's nth order, along with the Value of that order. I also have an aggregate measure of Potential Orders at the member level, which shows how many orders the member could have made (without going into too much detail, this is mostly based on when they joined. So a member that joined last week has had less Potential Order opportunities than a member that joined 4 weeks ago). For each Order Number, I want to track how many members have made that many orders as a percentage of members that could have made that many orders.  The first part of the requirement is easy - I can just place Order Number on the shelf and count the Number of Records. However, this alone misses the nuance of Potential Orders. It looks like retention from Order 1 to 2 is 66% (2 of 3 the total members have made a second order), but only 2 members (Members B

  • Issue#339632: Tableau - issue with CAGR calculation

    Hi All, we have create a CAGR calculation by using Lookup(): (LOOKUP(SUM([Month Totals]),LAST())/LOOKUP(SUM([Month Totals]),FIRST()))^(1/(SIZE()-1))-1 Formula will only work, if all years are visible.Now I am searching for a way, how to adjust the formula to be able to display only a few years.Can I refer to value for 2012 and 2016 and also lookup the specific years instead of using SIze() within formula?  Thank you very much in advance for any hints. Best regardsSabine

  • Issue#340617: Tableau - Calculate a field based on a previous year's data

    Hello all, How can I calculate a field for a certain year using data from a previous year.  I want to Calculate 2015 and 2016 Revenue as a Percent of 2014 Expenses. Thank you!

  • Issue#401955: Tableau - Data Blending Issue

    Hi All, I am working with two data sources and I need to blend some data fields from both data souses to create some dash boards. I did data blending but it does not give me correct results what I expect. I have attached a sample workbook by enclosing similar example which I faced. For your convenience, I have attached two screen shots of results what I expected and observed 1. This kind of figure I am expecting to have. In my two data sets one have 5 ICUs(A, B, C, D, E) and 3 for other(A, B, D) . But this figure shows 3 ICUs(A, B, D) which are in data set 1. But I need to show rest ICUs though those do not have data (C and E) 2. When I blend Data I observed this figure. This figure shows rest 2 ICUs(C and E) But failed to show discharge type correctly as in figure 1 when dragging discharge type into color shell Can anyone please kindly help me to getting this problem solved. I have attached my tableau workbook as well. I can work upto tableau version 9.2. 

  • Issue#402880: Qlik - Comparison with previous month data issue

    Hi All, I have an issue while comparing this month data with previous months data.I have fields like product name ,Sale ID sold and Month. For each sale there is one SaleID associated.i need to compare the products items sold for each product with last months products and need to display the products under Improvement and Deterioration sections.If any product has got more no.of products sold when compared to previous month it should come under improvement else it should come under deterioration. To achieve this i have taken List box and instead of field i took expression and in expression for improvement i have considered the below expression. Improvement: If(Aggr(Count({<Month={"$(=Month(Max(Date)))"},Year={"$(=Max(Year))"}SaleID),Products)<Aggr(Count({<Month={"$(=AddMonths(Month(Max(Date))),-1)"},Year={"$(=Max(Year))"}SaleID),Products),Products but the issue is all products doesnt have sales in all months. while comparing the products comparison happ