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 clarification....help me my work is totally stuck. Regards,Nakul

  • 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#669600: Tableau - Create calculation with value in a data set

    I have those Data (data type filed) Hiring and Departure and i want to create some calculation or formula to create a tableau like my excel sheet.  I want to be able to do the sum of Departure and Depart temporaire and the same thing with Hiring and Rappel See attach printscreen  thanks

  • 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#91285: Tableau - How to Plot Value Over Time When Data Set Has Only 'Start' and 'End' Dates

    Hey Everyone, I have a seemingly simple problem that has stumped me and many Tableau "Doctors", and I'm hoping you can help me out.  I have a data set with a 'start month', an 'end month', and a 'monthly value', and I'm looking for a way to plot monthly value over time.  Here is a simplified version of the data set I'm using:   Customer NameMonthly Invoice AmountInvoice Start MonthInvoice End MonthCompany A$200January 2015July 2015 I've tried all sorts of calculations and techniques to allow me to plot 'total invoice amount' by month, but the only way I have been able to accomplish this successfully is by cross joining my data set with a table containing all months present in my data set, so that I have a row for each company and month.  So, for "Company A" in my data above, the new data set resulting from the cross join would look like this (7 rows instead of 1).   Customer NameMonthMonthly Invoice AmountCompany AJanuary 2015$200Company AFe

  • 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#107091: Qlik - Table with a data set

    Hello everybody, I have a problem and I don't know the solution. I like to do a simple table with a ID and other calculates ID  Sum(field1)  sum(field2)  I have many ID but I like to have only a set of these, for example ID  sum(field1) 1  1000 2  3000 3  4000 4  1000 I like what my table shows ID = 2 and 3. I would like the next table: ID  sum(filed1) 2     3000 3     4000 In my real example, I make a set :  only({(<field2 = $::field2>) - <IDA= $::IDA> * (<field1 = $::field1>)}ID)but I'm not as to do what my table only shows the IDs of the set¿How can I have my table?Sorry for my bad EnglishRegards

  • Issue#109305: Tableau - creating a set with the <> condition

    Hi I am crying to create a set with the condition that the customers with sales that lie between 1e6 and 5e6. How can I create this? I started by selecting the condition that has the symbol <> but then how to enter the data for this condition? Should the two numbers that are required to specify the lower and upper limits be separated by comma or what? I could not find anything in the documentation.  Thanks,Bhushan

  • 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#138701: XY Graph, initialize multiple sets of data, and update only one set of data

    Hello, I have been developing an VI to control the x-y coordinates of a platform using two motors. A somewhat psedo version of xy-plotter. The detail I could use help in is the following: 1. I have 469(or 400~ish, there are two modes) discrete points to be initialized/logged into an x-y graph such that a cursor on the x-y graph can snap to the discrete points. This far is done.2. I have to keep track of the current x-y position. This is done as well3. I have to keep track of old x-y positions, where I was able to extract data properly. This is done by a switch.4. pretty much the entire VI is under a one giant while loop, properly sequenced by 0's being passed from one case structure to another until it reaches the final "global dummy". Now, the signals that  are being processed to the motor is through a USB Digital signal generator, I don't think specifics are necessary. Essentially I needed to bit-mask each individual movement, so the "motor mov

  • 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#177438: Issue while adding a field from the data set(fetched through a SSAS cube via live connection)

    Hello, I am facing issue while adding a field(datatype: String) from the data set in my report. The data set is being fetched from a SSAS cube through a live connection.The message I get every time is: The given key was not present in the dictionary. I am able to browse this field in Cube and also I can browse the values to this field when I make a Excel pivot. Thank you

  • Issue#180908: Tableau - Kaggle Data Set

    For tomorrow's hands-on Tableau session, we'll be creating data visualizations using this Kaggle Data Set:Dropbox - Birmingham TUG

  • 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?