4 Replies Latest reply on Jun 21, 2017 9:36 PM by susheela.hegde

    how to display week data but grouped on month

    susheela.hegde

      hi,

      I have to display weekly data but have to group on month also I ahve to blend the data here.

      my primary source has first day of the week calculation.

       

      Id    weekdate

      1     2017-01-01

      2     2017-01-08

      3     2017-01-15

      4     2017-02-05

      5     2017-02-12

       

       

      My secondary source has mid month as date

      Date              measure

      2017-01-15        12

      2017-02-15        1000

      2017-03-15        450

      2017-04-15        6000

       

      So I have to join these two tables(these are in different servers) and pull sum(measure) for weekdate in primary source.

      for all the weeks in same month in weekdate,  should have respective month measure.

      output:

       

      weekdate    measure

      2017-01-01  12

      2017-01-08  12

      2017-01-15  12

      2017-02-05  1000  and go on.

       

      With my current calculation, when I drag weekdate in my view, measure is aggregated at week level and not staying at month level.

       

      I tried using Fixed LOD. but getting errors like we should have fields from same source or cannot mix aggregate ot constant error.

       

      Can anyone of you please help me?

        • 1. Re: how to display week data but grouped on month
          Wilson Po

          Hi Susheela,

          It seems like the best way to join these two tables is to leverage Join Calculation to align the data at the month level.  You can sync the dates by rounding each date the beginning of each month using the DATETRUNC() functionality.  This essentially shifts any day and rounds to the beginning of the period defined in the function:

               DATETRUNC('month',#2017-01-15#) = #2017-01-01#

          Using this logic, I would join based on the following logic:

               DATETRUNC('month',[Date]) = DATETRUNC('month',[weekdate])

          You may need to also use these instructions as well as your data sounds like it is sourced from different servers: Join Your Data (Different Database)

          • 2. Re: how to display week data but grouped on month
            susheela.hegde

            Hi,

            thank you. my sources are from different servers. I am unable to join the tables from different servers. in the link you sent has how to connect to different server and we have to create .tde file. However, I am unable to create .tde file. When I click on extract it just says extract created and not popping up anything where i can save the file.

            • 3. Re: how to display week data but grouped on month
              Wilson Po

              Hi Sushella,

              I think you are mistaken - Tableau v10 can join tables from different data sources and it does not require extracting the data out as a tde. You can maintain cross-database joins as a live connection;  it may make sense to extract to improve performance, but should not be a requirement.

               

              Additionally, from the description above - it sounds like the extract was created regardless.  Is your file a packaged workbook file (*.twbx)?  If so, Tableau automatically packages the extract in the packaged workbook file without needing the user to define a "Save As" location for the TDE.  Its part of just streamlining the process when users toggle on that extra option. 

               

              I would go back to the documentation I linked before and verify the steps right there.  It sounds like it should work out as long as you are using a recent version of Tableau. 

              • 4. Re: how to display week data but grouped on month
                susheela.hegde

                Hi Wilson,

                Sorry for being silly. I have joined the tables before. but this time, I can see only once connection though I have connected to two data sources. I have to switch to other connection to see other table .Can you please tell me what is the mistake I am doing here? any idea?

                Please find the below screenshot.I have joined two sources but doesnt give me joining option and allows me to switch the source only.