10 Replies Latest reply on Jul 19, 2018 12:19 AM by Hanane Ouhammouch

    Use historical data in a geo map

    Hanane Ouhammouch

      Hi Folks ,

       

      I would like to use  historical data in a map

       

      More precisely, I have a map  where I display indicators by department

       

       

      These indicators change over time and I want to keep history of the changes

       

      concretely: I have a table where  I capture the changes with a  scd_version column which is  incremented once a new version is created

      The columns of my table are:

      Departement || Country  || Nbr    || scd_version

      if i select "1" from the SCD Version filter i will have all line with scd_version = 1

       

      on the other hand if I select "2" from the SCD Version filter I would like to have the line with scd_version = 1 without the departments  whose  new lines have scd_version columns  value equal  2 +departments with columns scd_version = 2

       

       

       

      is it possible ? .

       

      thank you very much for your help

        • 1. Re: Use historical data in a geo map
          nikher verma

          Hi Hanane Ouhammouch ,

           

          Yes it is possible , but you need to attach your twbx file .What I understand from your screen shot is you have longitude and latitude for all province and scd version .So just create the viz and put SCD_Version in filter that you already did .

           

          I mean you already have attached the screen shot of what you are looking for or are you trying to achieve a viz like this .

           

           

          -Nikher Verma

          • 2. Re: Use historical data in a geo map
            Hanane Ouhammouch

            Please find attached the workbook

             

            the second image is what it display

             

            and what i want is (

            if i select "1" from the SCD Version filter i will have all line with scd_version = 1

             

            on the other hand if I select "2" from the SCD Version filter I would like to have the line with scd_version = 1 without the departments  whose  new lines have scd_version columns  value equal  2 +departments with columns scd_version = 2)

             

             

             

            thanks

            • 3. Re: Use historical data in a geo map
              Hanane Ouhammouch

              I have a question , can we handle historical data with tableau

               

              Creating a Data Extract with Today's Data While Maintaining Historical Data When Refreshed | Tableau Software

               

              i found this link , if some one have an exemple that will be realy helpful thank you

              • 4. Re: Use historical data in a geo map
                Michael Gillespie

                Hanane, for your first question, it is not clear what you want to accomplish.  The normal behavior of a filter is to restrict the view to rows that contain the value selected in the filter.  That means if you select "2", Tableau will display départements where the scd_version column=2.  But it sounds like you want some other kind of result.

                 

                Pourriez-vous l'expliquer en français?

                 

                For your second question, I don't understand what you're asking.  What problem are you trying to solve that is not addressed by that link?

                • 5. Re: Use historical data in a geo map
                  Hanane Ouhammouch

                  Bonjour,

                  ce que je voudrais  faire c'est gardé des vues historiques de ma carte.

                  Le faite de faire un snapshot chaque jour pourrait résoudre le problème  mais la table deviendra énorme avec le temps alors je me suis dit d'ajouter  une nouvelle ligne que si la valeur de l'indicateur à changer

                   

                  Par exemple :  

                   

                       département || nbr || scd _version

                  1   79                       3       1

                  2    74                       4        1

                  3    73                       6        1

                  4     73                       10       2

                   

                   

                  si je sélectionne Scd version =2  je voudrais prendre en compte des lignes (1,2 et 4)

                  si je sélectionne  scd version =1 je voudrais prendre en compte des lignes (1,2 et 3)

                  possible ?

                   

                  Pour ma deuxième question selon ce que j'ai compris du lien c'est  une possibilité de garder un historique des extraits à utiliser  (ça va répondre à mon besoin) mais je n'ai pas vraiment compris comment le faire  Merci pour votre aide

                   

                   

                   

                   

                   

                  • 6. Re: Use historical data in a geo map
                    Michael Gillespie

                    Ah, je croix que je vous comprend mieux maintenant.  Je vais répondre en anglais...

                     

                    You are trying to display the most recent data for a given département.  So, if there are 2 records for "25", then you want to show the most recent record if you select any value other than "1" for the scd_version column.

                     

                    Let me work on that and post an updated workbook.

                     

                    For your second question, what is the original source of your data?  A relational database (Oracle, SQL Server, MySQL, etc.)?  Or a spreadsheet or CSV file?

                    • 7. Re: Use historical data in a geo map
                      Hanane Ouhammouch

                      Thank you very much for your help

                       

                      for your question , My original source is mysql

                       

                      Thanks

                      • 8. Re: Use historical data in a geo map
                        Hanane Ouhammouch

                        Any new about this case please

                         

                        Thanks

                        • 9. Re: Use historical data in a geo map
                          Michael Gillespie

                          Bonjour, Hanane.  J'ai enfin eu assez de temps pour vous répondre.

                           

                          Look at Sheet 2 in the attached workbook.  I used your Parameter to drive the selection of the SCD_Version value.

                           

                          This ONLY works for a limited case - where you have a value of 1 or 2 in SCD_Version.

                           

                          Sheet 3 shows you how the new calculated fields Max SCD Version and Pick Version work.

                           

                          Basically, I'm setting a new flag for each row of the data set to determine whether or not to display the row.

                           

                          Questions?

                           

                          Bonne journée!

                          1 of 1 people found this helpful
                          • 10. Re: Use historical data in a geo map
                            Hanane Ouhammouch

                            Bonjour Michael , Merci beaucoup pour ton aide

                            j'ai trouvé une autre solution.

                             

                            1. Create a query with column date as now()

                            2. Use the result of the query as a source in the workbook.

                            3 Create an extract and use the option  Incremental Refresh based  on the column date (the new column i added).

                            4. Program the refresh every day

                            5. Use this date as a filter in my dashboard (Geo MAP)

                             

                            Thanks

                             

                            Bonne journée également!