6 Replies Latest reply on Aug 28, 2016 10:42 PM by Shesil Parmar

    Is it possible to store value from calculated field somewhere ?

    Shesil Parmar

      Hi

       

      I'm trying to create a month after month graph. in excel i run a report and copy numbers and paste every month behind previous months' number (attached pic) but in tableau it gives only current numbers - i understand that my calculated fields looks at current numbers hence its producing in such a way. if i run report next month than it just keep addiing numbers but does not retain their old number. hope i m not confusing !

       

      attached is excel graph and tableau graph. sorry data are so big , will have to do some work to create dummy data...

       

      Untitled.png

      thanks

        • 1. Re: Is it possible to store value from calculated field somewhere ?
          Simon Runc

          hi Shesil,

           

          So as you have probably realised Tableau and Excel are very different tools, and think in very different ways...

           

          So it looks like you have (somewhere) a raw data report, which you then extract the numbers you need from (the manual copy and past bit) and then put them into that specific format, so you can create the chart in Excel. Tableau would (generally) rather just have all the raw data (again, generally) in a column/normalised table (so Date going down the page). From here it can (with a few calculations) do the Month-on-Month/Year-on-Year...etc. calculations. You can then bring in the new data the next month (so you'll still have all the months + the new one) and when you refresh the data, it will just feed in (so the calcs) and the chart will be be updated to the next month. It's much more like a database where the visualisations are queries over the data.

           

          If you can post a mock-up (or anonymised) version of the raw data you get I can show you how to create the chart.

          • 2. Re: Is it possible to store value from calculated field somewhere ?
            Shesil Parmar

            thanks simon for prompt response.

             

            i have attached a packaged workbook.

             

            so basically, every month i run a report and it keeps adding number for new month and update charts - it also updates all past months' numbers as well.

             

            i do not want old numbers to get change, but add new new line for new numbers in chart (only last 12 months)

            • 3. Re: Is it possible to store value from calculated field somewhere ?
              Simon Runc

              hi Shesil,

               

              So I think I understand your requirements...and probably the way to go is to use 'Append Data'. This will require you to create an extract from your current data, each month you then re-run your report (but only for the new month), and have this as a separate file...and then you can use the 'Append' data from this new file. This will have the affect of only adding the new months data to your Extract, but won't touch the data that you previously loaded.

               

              So in my mock up I have the original data I load to build the extract, going up to September

               

              I use Extract Data, to create an extract...

               

              Then I have the next month's data in a separate file

               

               

              To add this data to the Extract, without changing and of the rows to September, I use the 'Append Data from File'

               

               

              This will bring up a choose file box, where you can select the new months file, and these 3 rows will be added to the Extract, with the other rows untouched (even if the original file changes...btw in your scenario DO NOT select Refresh Extract, else it will go back a re-build the extract from the original file, and if the data has changed that would be reflected in the extract.

               

              Not perfect, but you have a rather unusual situation where the latest run of data changes the history, which you don't want. Tableau tends to make things that happen 90% of the time quick and easy, and bit more work for the 10% of exceptions (which I think is sensible)

              • 4. Re: Is it possible to store value from calculated field somewhere ?
                Shesil Parmar

                thanks simon and apology for late response, i was trying to work it out how to put this in working module.

                 

                i think i will have to keep separate s/sheet for the records and link it from excel. but now facing another issue with graph, i have attached a spreadsheet and graph which i want - but so far not able to achieve that

                 

                graph required :-

                • 5. Re: Is it possible to store value from calculated field somewhere ?
                  Simon Runc

                  hi Shesil,

                   

                  So (as mentioned before) Excel is quite a different beast to Tableau...and as a data-base (based) technology, Tableau (generally) wants data in a normalised/table-down format. So you're data is in the wrong shape for Tableau.

                   

                  Not to worry, Tableau has a little feature to re-shape the data from Excel.

                   

                  So when I connected to your sheet, I used the PIVOT tool on the data-connector to pivot the data. So I want the 'Month After Months of Travel' as a dimension (going down the page)

                   

                  This does this

                   

                  I can then re-name the Pivot Field Names (to 'Month After Months of Travel') and Pivot field values (to 'Value').

                   

                  Once we have it like this, creating the chart is super easy.

                   

                   

                  btw the Pivot is built into the data definition...so if you put some new data in your excel, and you refresh the data/extract the data will come into Tableau pivotted (so no need to re-apply the pivot when you update the data)

                   

                  Hope that helps and makes sense.

                  1 of 1 people found this helpful