2 Replies Latest reply on Jan 1, 2019 11:56 AM by Emir Karadag

    Peer Grouping & YTD Growth calculations in Table Calculation

    Emir Karadag

      Hi all,

       

      Happy New Year!

       

      I have two interesting problems I have created using some dummy data (see attachment1 - Dummy data). I'm somewhat new to the data visualization part, especially to Tableau so please bear with me when I don't use the right terminology or explain a simple concept in a complicated matter.

       

      Also, I do not usually post on the forums as I try to make use of the already answered problems but I have had no luck in the past weeks finding what I want to do and I heard that this community is so welcoming, I wanted to give it a shot.

       

      Let me get right into it:

       

      Data-set explanation:

      The data set consists of the monthly sales for each product in the span of 3 years.

       

      The numbers in the dummy data I created do not reflect the real numbers since they are just randomized. The Sales Pre column is supposed to reflect the sales numbers from the month = (current_month -1) AND (year = current_year - 1).

       

      Problem 1: I want to be able to show the percentage change from the previous year but 2018 percentage change should be based on the YTD calculations.

       

      Desired End result: I want two panes in the table; one with sales numbers and another pane that shows the FY Percentage Change for 2017 and YTD Percentage Change for 2018. I don't need to show the sales numbers from the previous year. So no Sales-pre pane and removed unnecessary years (see Attachment2 - Screenshot)

       

      Current state: I have added (behind the scenes in Python) the Sales Previous column which shows the sales in the current month previous year.  I tried to do two separate table calculations but it does not work because 1. I can't get rid of the unnecessary years in the view because for instance once you get rid off '2016', it is removed from all the panes not only from the Growth pane.

       

      I can make a table calculation using the Sales_Pre and Sales measure values but I do not know how to create a pane out of multiple measure values. Because if I were to calculate the percentage difference between:

       

      2017 Sales and 2017 Sales Pre -> gives you FY 2017 percentage change because 2017 Sales gives you the all the sales happened in the full year of 2017 and 2017 Sales Pre gives you all the sales that happened in the full year of 2016.

       

      2018 Sales and 2018 Sales Pre -> gives you YTD 2018 percentage change because 2018 Sales gives you the YTD 2018 Sales and 2018 Sales Pre gives you all the sales that YTD 2017.

       

      These might not be clear in the data set but essentially in my data source it is set up like this.

       

      I also thought of creating calculated fields specifically for 2017 FY percentage change and 2018 YTD percentage change but I could not figure it out due to the dimensions and how to properly align the granularity of your data to these calculated fields.

       

      Problem 2: In my data set you can see, there are three more columns; Group 1, Group2 and Group3.

       

      I have added these columns using another source of data (see attached Attachment3 - categorization) to identify the child-parent-peer relationship between all the products.

       

      For instance: Product A is the parent of product AA meanwhile product B is the peer of product AA. Parents are automatically the peers of the child and we only care about the peers of the child. Because we are trying to understand only the child's performance against its parent and its peers.

       

      There is a one-to-one parent-child relationship and one-to-many child-peer relationship.

       

      In my data source, Group1 columns is never NULL because they are either the peer of another product because they are either a parent, a peer or the child themselves. Group2 is not NULL when for example they are a parent to a child and a peer to another child and so on with Group3. I have these columns up to Group4 in my real data source.

       

      Desired End result:

       

      The user will have the option to filter the children only and only one child can be selected at a time. I would like to show the user the child (they have selected) along with the child's parents and its peers.

       

      Current state: I started creating a group to group the child and parent together but I'm really stuck with showing the right peers. I can essentially group all the child-parent-peers together on Tableau manually but I would like to know if there is a more intuitive way of doing it.

       

      Please let me know any solutions you can think of. I would welcome different approaches to these problems because most of the times it is better to adapt to Tableau and rather than trying to push old habits into new tools.

       

      Best,

      Emir

        • 1. Re: Peer Grouping & YTD Growth calculations in Table Calculation
          Norbert Maijoor

          Hi Emir,

           

          Upfront!: HAPPY NEW YEAR

           

          Find my approach for problem 1 as reference below and stored in attached workbook version 10.5 located in the original thread.

           

           

          1. D1. Date: date(str([Year])+'-'+str(int(([Month])))+'-'+'1')

           

          2. P1. date Parameter based on Date and only keep entries for 2018

           

           

          2. M1. 2016: If datediff('year',[D1. Date],[D1. Date Parameter])=2 and [D1. Date]<=dateadd('year',-2,[D1. Date Parameter]) then [Sales] END

           

          3. M2. 2017: If datediff('year',[D1. Date],[D1. Date Parameter])=1 and [D1. Date]<=dateadd('year',-1,[D1. Date Parameter]) then [Sales] END

           

          4. M3. 2018: If datediff('year',[D1. Date],[D1. Date Parameter])=0 and [D1. Date]<=dateadd('year',0,[D1. Date Parameter])then [Sales] END

           

          5. M4. % change 16>17: (sum([M2. 2017])-sum([M1. 2016]))/sum([M1. 2016])

           

          6. M5. % change 17>18: (sum([M3. 2018])-sum([M2. 2017]))/sum([M2. 2017])

           

          7. Drag the required objects to the indicated locations and show parameter control.

           

          show.png

           

           

          Problem 2......i this moment I can't figure it out what you are exactly aiming for. Please elaborate once again.

           

          Hope my approach is sufficient.

           

          Regards,

          Norbert

          • 2. Re: Peer Grouping & YTD Growth calculations in Table Calculation
            Emir Karadag

            Hi Norbert,

             

            Thank you very much for the solution to the first problem. It worked perfectly!

             

            In the second problem, I'm basically trying to show all the peer products of the selected product.

             

            For instance; AA, BB and CC are spin-off products of A, B and C respectively. I want to show the user all the peer products of the selected spin-off product. I have the columns Group1,Group2, and Group3 that indicate which peer groups the product belongs to.

             

            I have created a filter in Sheet1 in v10.6 and want to show only the relevant peers according to the selection but I have trouble creating the custom logic based on the Group1,2,3 columns

             

            Thank you very much again!

            Best,

            Emir