14 Replies Latest reply on Nov 20, 2018 1:18 AM by manh huy nguyen

    [MTD data] vs [Last 12 months data] vs [Since January data]

    manh huy nguyen

      Good morning,

       

      See my attached Excel file to better understand my first question.

       

      • I have 2 different datasources: "Country" and "Sales".
      • Commun fields are Country and Period.

       

      • In Tableau, I will need to create a parameter with 3 sorts of calculation:
        1. MTD Data                                        <------ (sum of sales / for each month)
        2. Last 12 months data                        <------ (sum of sales for the last 12 months / for each month)
        3. Since January of this year data       <------ (sum of sales since January of current year / for each month)

       

      • I would like to be able to create this 3 calculated field as mentionned here above but i have no clue how to do this.

              You can see the results expected in my Excel sheet called OUTPUT (column F, column I, column L).

       

      • So in Tableau, if I filter on [Period] = [Oct 2018] and [Country] = [Belgium]
      • I would like to see/get:

      MTD data = 4

      Last 12 months data = 42

      Since Jan of this year = 29

       

      • I don't know if i have to use
        • one data source connection (join between these 2 tables) and how ?
        • 2 data sources connections (one for each table) and blend data between each other, and how ?
        • or maybe another way that i haven't thought of yet.

       

      When this will be solved (thanks to your help), i might have another question that will go a bit beyond this initial query.

      I will need to add in these new 3 fields some conditions on "Country_Category" and "Country_Manager" dimensions..

      But i will detail this later on..

       

       

       

      Edit 2:

      I have attached a Tableau Workbook (twbx)

       

      Edit 3:

      My post hast been more detailed in below post:

      https://community.tableau.com/message/846932#846932

       

      Thank you for your assistance.

       

      Manbboy

        • 1. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
          Norbert Maijoor

          Hi Nguyen,

           

          Checked your file tab's Country & Sales. In your Country-tab there is only data for 2018 where the sales-tab has data for 2017 & 2018.

           

          I assume you would like also to have data for Country in 2017. Please add

           

          Regards,

          Norbert

          • 2. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
            manh huy nguyen

            Hello Norbert,

             

            Data that I have provided is only a sample, used to ease my explanations. Numbers are fictive.

             

            But to reply your question, the scope of my dashboard will start from January 2018. Since I need to have the number of sales from the past 12 months, I have added sales data beginning in January 2017.

            My data set is then complete.

             

            Ps: New data will be appended in the future (december 2018, january 2019, february 2019, etc.).

            • 3. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
              Norbert Maijoor

              Hi Nguyen,

               

              Oke, You don't need to join the two tables to get your requested answers

              Hguyen.png

              FInd my approach stored in attached workbook version 2018.3 located in the original thread

               

              Regards,

              Norbert

              • 4. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
                manh huy nguyen

                "This file was created by a newer version of Tableau. Please upgrade Tableau to the version (or greater) that the file was saved in."

                 

                I'm running Tableau Desktop 10.4.6. I can't upgrade it to the latest version (professionnel reasons).

                 

                Do you have another way for sharing your twbx?

                Or could you show me your calculated fields ?

                 

                Sorry for that:(

                 

                 

                PS: "You don't need to join the two tables to get your requested answers"

                Just to make sure my demand is well understood. I will need to start from my first datasource "Country" and join the sales,

                because i might have new countries that coming in (in the first datasource) but wihtout sales (second datasource). But i would need to see these new coutries in my list but with 0 sales..

                I hope i made my initial demand clearer.

                 

                Thank you for your help.

                • 5. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
                  Norbert Maijoor

                  Nguyen,

                   

                  Fjnd attached workbook in verison 10.3

                   

                  Hope it helps.

                   

                  Regards,

                  Norbert

                  • 6. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
                    manh huy nguyen

                    Your attachment seems not to be the good one ("Pivot nalmai_V10.4 (1)_v10.3.twbx")

                    Sorry for spamming..

                    • 7. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
                      Milan Mallick

                      Hi,

                       

                      First of all You need to populate the Country data for 2017 then only you can run your Query.

                       

                      Create 02 Parameters.

                       

                      1. Period ----->copy the data from period field.

                      2. Fact     ------1> MTD

                                             2> Last 12 Months Data

                                             3> Since Jan till this year Data.

                       

                      Create Fact_Period as Calculated Field

                       

                       

                      CASE [Fact]

                          When '1' Then

                              [Period]=[Parameters].[Period]

                          When '3' Then

                              [Period]>=DATETRUNC('year',[Parameters].[Period]) and [Period]<=[Parameters].[Period]

                          When '2' Then

                               [Period]>=DATEADD('month',-12,[Parameters].[Period]) and [Period]<=[Parameters].[Period]

                      END

                       

                      Put this into filter and Choose True Only.

                       

                       

                      • 8. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
                        manh huy nguyen

                        Thank you Milan Mallick,

                        Thank you Norbert Maijoor,

                         

                        I have attached in my intial post my worbkook.

                        To get it easier for you to assist me.

                         

                        Mallick, could you share your workbook as well ?

                        • 9. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
                          Norbert Maijoor

                          Hi Nguyen,

                           

                          Apologize. find attached in version 10.3

                           

                          Regards,

                          Norbert

                          • 10. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
                            manh huy nguyen

                            Thak you very much for your help Norbert.

                            Much appreciated.

                             

                            However, what i need is

                            - the use of a parameter (to switch from MTD, Last 12 months, Since January) for the same view

                            - the view should display Month(Period) on the column pile so i can see from January 2018 to November 2018 the sales data

                             

                            For a better understanding of my query, please find attached my worbook.

                            The view in sheet1 is the one i need.

                            I Just need now a parameter with calculated fields for Last 12 months and Since January.

                             

                            I will try to reproduce Milan Mallick solution tonight.

                            But if you (Milan) can already share your worbkook, this might help me.

                             

                            Thanks both of you so far.

                            • 11. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
                              Norbert Maijoor

                              Hi Nguyen,

                               

                              Find my updated approach as reference below and stored in attached workbook version 10.3 located in the original thread.

                               

                              1. M1. MTD: if datediff('month',[Period],today())=0 then [Nbr of Sales] end

                               

                              2. M2. YTD: if [Period]<=today() and year(date(today()))=year([Period]) then [Nbr of Sales] END

                               

                              3. M3.  Last 12 month: if datediff('month',[Period],today())<=11 and datediff('month',[Period],today())>=0 then [Nbr of Sales] END

                               

                              4. M4. Display Metric:

                              if [Select Period]='M1. MTD' then [M1. MTD]

                              elseif [Select Period]='M2. YTD' then [M2. YTD]

                              elseif [Select Period]='M3. Last 12 Month' then [M3.  Last 12 month]

                              END

                               

                              parameter.png

                               

                              Regards,

                              Norbert

                              • 12. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
                                manh huy nguyen

                                Norbert,

                                I really appreciate your efforts for giving me solutions.

                                Thank you.

                                 

                                However, this is not exactly what I need.

                                Let me briefly recap all I need:

                                 

                                - I do need the 2 data sources to be linked somehow

                                I can have a new country in December 2018 in my first data source and having no sales related in my second data source. But I will need to see my new country in my row shelf with 0 sales. So Country dimension should be used from the main data source, number of sales from the second data source.

                                 

                                - could you please have a look on the twbx that I have uploaded ?

                                I need the same view (split by month, from January 2018 to december 2018).

                                The view (12 columns for an entire year ) should remain the same whatever the parameter chosen.

                                 

                                - by selecting MTD parameter, this should display the Sum(Nbr_sales). Easy.

                                - by selecting LAST 12 MONTH, it should display for each month, the number of sales from this month for the last 12 months. So below January 2018, I should have the sum of sales from January 2017 to January 2018. Below February 2018, I should have the sim of sales from February 2017 to February 2018, etc.

                                 

                                 

                                - actually, from my Excel spreadsheet called "output", everything that I need is there.

                                For each combination country + month, I need 3 calculated fields.

                                 

                                Seeing so many confusions, I will have to finetune my original post.

                                 

                                Sorry for the misunderstandings.

                                 

                                But thank you a lot.

                                 

                                Looking forward for a solution

                                • 13. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
                                  Milan Mallick

                                  Hi,

                                   

                                  In addition I am going to attach the process of doing the same instead of providing twbx. Hope this will help you.

                                   

                                   

                                  As per your attached twbx.....

                                   

                                  Parameter : Choose Matric

                                   

                                  Create Another Parameter....Period

                                   

                                   

                                  Data type would be Date.

                                   

                                  In display format... choose custom and put the format as per screenshot

                                  Allowable value will be list

                                  Choose Add from Field in the right side. Choose Period.

                                   

                                   

                                   

                                   

                                   

                                  Then Create a Calculated Field. Fact_Period

                                   

                                   

                                  Fact_Period

                                   

                                  CASE [Choose Metric]

                                   

                                   

                                      When '1' Then

                                   

                                   

                                          [Period]=[Parameters].[Period]

                                   

                                   

                                      When '3' Then

                                   

                                   

                                          [Period]>=DATETRUNC('year',[Parameters].[Period]) and [Period]<=[Parameters].[Period]

                                   

                                   

                                      When '2' Then

                                   

                                   

                                           [Period]>=DATEADD('month',-12,[Parameters].[Period]) and [Period]<=[Parameters].[Period]

                                   

                                   

                                  END

                                   

                                  Put the Parameter Period as Show Parameter Control.

                                   

                                  Remove your Period Filter.

                                   

                                  Put the Fact_Period into the Filter and Choose 'True'

                                   

                                  Now Check with your Parameter "Choose Matric"

                                   

                                  But I had already told you that This will Never be able to Show the data from Nov 17 as the data set is not available in the Country Datasource.

                                   

                                  Regards.

                                   

                                  Milan

                                  • 14. Re: [MTD data] vs [Last 12 months data] vs [Since January data]
                                    manh huy nguyen

                                    Thank you Milan,

                                     

                                    We are getting closer but not yet exactly what i'm trying to explain you guys.. (again, sorry for that).

                                    But we are on the good way for the solution !!

                                     

                                    Here below is the screenshots of my wish (I have also attached the workbook of these screenshots).

                                     

                                    Explanations 1.jpg

                                     

                                    Explanations 2.jpg

                                     

                                    Explanations 3.jpg

                                     

                                     

                                    Calculated fild: Sales_depending_on_parameter

                                    CASE [Choose Metric]

                                    WHEN '1' then [Sales MTD]

                                    WHEN '2' then [Sales 12mo]

                                    WHEN '3' then [Sales Jan]

                                    END

                                     

                                    As you can see, based on my parameters, the layout of my view does not change. That is what i need.

                                    The columns should remain the same whatever the parameter selected.

                                    The only information that should be changed is the number of sales.

                                    - MTD sales

                                    - Last 12 months sales

                                    - Since January sales

                                     

                                    So, from a Business aspect, we can have the number of sales per month and per country across the year.

                                    But with 3 differents "period" views (MTD, LAST_12_MO, SINCE_JAN).

                                     

                                    The problem in this attached workbook is that my datasource is the Excel spreadsheet called OUTPUT.

                                    I have already calculated via Excel formulas my Tableau calculated fields in this spreadsheet.

                                    What i am trying to do now, is the same but without the sheet OUTPUT.

                                    I need to use my 2 datasources "COUNTRY" and "SALES" and find a way to create the 3 news calculated fields via Tableau.

                                    1 of 1 people found this helpful