7 Replies Latest reply on Mar 1, 2015 11:43 AM by Bruce Segal

    spreading value across financial year

    Jagjit Singh

      Hi All,


      We evaluate property rents every six month against the market rent so in the rent review table a record is created only when the rent review process occurs. I'm now working on a report where I have to show the market rent for each property as per financial year.

      For Example: So if the rent view occurred in June 2104; the report should list the same market rent from June 2014 to Nov 2014.

      The attached excel file gives better clarity which contains same data and output required.




        • 1. Re: spreading value across financial year
          Noah Salvaterra

          Hi Jagjit,


          Using the table at the top of your spreadsheet as input, I took a crack at matching the output it in the attached workbook. Let me know if that looks good and I can walk you through the steps. It isn't the simplest thing to do in Tableau with your data structured in this way, so if restructuring your data is a possibility we could discuss that route as well.



          • 2. Re: spreading value across financial year
            Jagjit Singh

            Thanks Noah for looking at this. Is it possible to get the start month from July 2014 instead of December 2014 please. It will be difficult to amend the data as its an SQL database managed by our vendor. I just posted a sample data for 3 properties.




            • 3. Re: spreading value across financial year
              Noah Salvaterra

              Sorry, I looked at the file I attached previously and it doesn't seem to be quite right. In any case, restructuring your data need not require changes to the database. This can be accomplished via custom sql in Tableau. In this case the query would be something similar to this:


              select a.[Prop Code],


                   a.[Prop Rent Start Date] AS [Prop Rent Date],

                   a.[Prop Start Date] AS [Prop Date],

                   'Start' as DateType

              from [Rent_Data#csv] as a

              union all

              select b.[Prop Code],


                   b.[Prop Rent End Date] AS [Prop Rent Date],

                   b.[Prop End Date] AS [Prop Date],

                   'End' as DateType

              from [Rent_Data#csv] as b


              That is the query I used, in your case each of the from clauses would point to your sql server table, instead of a csv file.


              The attached example demonstrates this technique and seems to match the desired output. Let me know if this helps.



              • 4. Re: spreading value across financial year
                Bruce Segal



                I've edited this to add an additional question.


                1. Now that you have the sql qry, Noah devised. Why can't you give it to your data vendor so it will run it, and deliver you the data in the shape you need?
                2. Will you ever want to deliver the results in anything other than a data table? See example of a visualization of the data below? If all you ever need is a table of data you might find using a different tool - like the free sql workbench - will streamline the process. See my question to Noah below asking if Tableau is really the right tool for a job that's just about reshaping data and extracting it into a table.



                • 5. Re: spreading value across financial year
                  Bruce Segal



                  I feel compelled to play devil's advocate here, and ask if all Jagit needs to deliver is a data table and not a graph, is Tableau the best tool for the job? His .xls shows "output" as a table and that's what you reproduced in Tableau.


                  I ask this b/c if so, then all he needs is to reshape his data. (Putting aside the fact that perhaps his data vendor should deliver what Jagit needs ...) And if that's the case, then this is a data shape problem more than a data visualization problem. In which case, it might be more efficient just to run your sql qry through anyone of a number of sql tools to get the data in a table in the structure Jagit needs.


                  Tableau adds steps to the process not needed when using sql to solve this problem. And Tableau is limited to 16 columns of data in a data export, so if Jagit ever wants to look at 17 months of data then he has to pull it out of Tableau in pieces and reconstruct it in his spreadsheet. And he hasn't fully solved his problem.


                  Now, after the data is reshaped, if Jagit and his team DO want to see graphs, then Tableau is THE tool to use for that. To demonstrate that, I tweaked your workbook, and added a line graph (image above in my response to Jagit).


                  I attach the file in this comment. It's in ver 9.0 while your original is in an earlier version. So if anyone needs it in ver 8.2 let me know and I'll create it ver 8.2



                  • 6. Re: spreading value across financial year
                    Noah Salvaterra

                    Hi Bruce,


                    A quick look at other questions from Jagjit reveal that he is using more than just tables. Granted information can be processed more rapidly in a visual format, if it is presented thoughtfully, but when precision is required particularly in the case where only a few values are involved a table might not be so bad (or at least an appropriate accompaniment). It is funny I should be taking this side, because I'm pretty anti-table in terms of the end products I produce, but they definitely have their place. At the very least, tables are a nice way to practice with the structural complexities within Tableau, as charts can hide a multitude of issues. It is definitely not something I'd discourage from being used on the forum.

                    Tableau is a powerful tool for visualization, which has led the IT group in my company to refer to it as "a niche tool for advanced visualization." This gets me a bit unhinged, partly because they haven't actually ever used the tool. I tend to think of it more as a mainstream tool for empowering people to work with data, and visualization is a key piece of that puzzle and there has been focus on that element because historically it has been underserved by other tools. Tableau is just as much a tool for data manipulation and analysis. In fact it is far from the most powerful in any of these areas, the magic for me comes from having a balance which allows me to iterate and explore quickly. There are many folks who don't use the full analytical capabilities of Tableau, table calcs and data blending aren't for everyone and densification, well that is for hardly anyone. I don't find it any more tragic when folks don't use the full visualization capabilities. I try to embrace all that I can accomplish with the tool, but that may just be me. There may be groups or companies where some folks have more emphasis on one area or another, but where working with a common tool is desirable. I agree they would do well to diversify, but don't see any reason to turn in their licenses at this point.

                    It is hard for me to say if it is the right tool for Jagjit based on one question. It isn't right for everyone, but it does work very well for me. I'm not sure I follow the point about sql tools, if you are saying that the query might be easier to write there or that a view might be preferable from a performance standpoint, I agree. But SQL alone doesn't make creating this table terribly easy either, so another tool might still be needed anyway. As far as the limitation on 16 columns, that is simply false. True, there is a limitation on 16 distinct header labels on a table, and anyone running into problems with that limit is doing something interesting indeed. Crosstabs can be very long though and that is exactly what we have here. Copy crosstab will allow the table to be pasted in another tool or in an email even without the filter at all, and that is 447 columns. Well over 16, and likely well below any practical limitation in this regard, no splicing needed.



                    • 7. Re: spreading value across financial year
                      Bruce Segal



                      Thanks for your thoughtful response, and for letting me know we CAN get more than 16 columns of data out of a worksheet if we use "copy a cross-tab."


                      Perhaps, my question about delivering data in a table was not well framed. I agree with you that there are many instances where using data tables in Tableau is useful, and perhaps required (e.g. understanding what's happening in a table calc I'm building.)


                      What I was trying to determine from Jagjit was, "Does Noah's sql query solve Jagjit's problem? And if so, what if anything, will Jagjit need Tableau to do with the data after that?"


                      I share your view of Tableau as a mainstream tool to empower people (for me business users, who may have no programing or sql background) to work with data. And, for me that means determining if our sales, cost savings, or process improvement efforts meet company's business goals, and if not how to improve our efforts. This is the most important value I derive in using Tableau.


                      Like you, I've encountered a ton of people who don't share our view of Tableau. They decide, without using it that, Tableau is everything from a graphic design tool to make pretty graphs (lots of pies and doughnuts), to a data extraction tool (b/c their I.T. dept won't help them), to a data cleaning and reshaping tool. (trying to fix dirty data after it gets into a dbase b/c they have no data quality process).


                      Even though there are better tools and processes for these functions, Tableau can help in all them. I get "somewhat unhinged" when it's used for solely for that secondary functionality. It's like using a Ferrari road car to run the Baja 500 race.


                      I look forward to Jagjit's answer.