3 Replies Latest reply on Mar 9, 2018 3:55 PM by swaroop.gantela

    Merch calendar build joining two data sources

    Brian McNabb

      Hi Everyone,

       

      I'm new here so I apologize if this is a elementary ask, but I couldn't find anything similar posted.

       

      What I'm trying to do is build out a merch calendar for when promotions are run across the year. Basically what I want to show is each week with a unique promotional event is being run by customer based on the Promo Strategy. I'm having a hard time getting the chart to fill out the way I would like. For example, If I have a promotion that starts in week 1 and ends in week 52 (52 week duration) I want the merch chart to populate in each week (Weeks horizontally and Customers vertically). Right now that example is only showing up in week one so I may be joining the fields incorrectly.

       

      I've attached a sample workbook.

       

      Thanks for your help in advance,

      Brian

        • 1. Re: Merch calendar build joining two data sources
          swaroop.gantela

          Brian,


          Welcome to the Forum.

           

          I'm not sure I quite caught the gist,

          but I think a technique that will help you is described here:

          CROSS JOIN with Tableau's join dialog

           

          I'm not sure if this will work with your full dataset type,

          but basically I joined your data to a sheet that just had

          every day listed from 12/31/2017 to 1/1/2019.

           

          Then the two sheets were joined on a calculated field of 1.

          Then it was filtered such that:

          [Lookup Date]>=[Event Start Date]

          AND

          [Lookup Date]<=[Event End Date]

           

          This method also allows you to add and plot how many promo's are

          on a given day.

           

          Please see if the attached workbook in the Forum thread is closer to your goal.

          Also attached is the xlsx.

           

          264234merch.png

          • 2. Re: Merch calendar build joining two data sources
            Brian McNabb

            Thanks, Gantela!

             

            This is very close to what I'm looking for. I've made a coupe of adjustments and attached that workbook here. I have it exactly in the view I'm looking for except for the fact that when a promotion runs over two week periods I will get the frequency double counted( example: 1 week or greater promotion starting in the middle of week 3 and ending in the middle of week 4).

             

            Here's an example of a retailer where the output shows 24 frequencies:

             

             

             

             

            However, there actual expected outcome is 12:

             

             

            I've tried to multiple formulas to to get to this output with no success.

             

            Thanks again for the help!

            Brian

            • 3. Re: Merch calendar build joining two data sources
              swaroop.gantela

              Brian,


              I took a quick look at it, and had some questions.

               

              1)

              I think you will need a level of detail calculation to count distinct regardless of the week.

               

              It would be something like this:

              { FIXED [Event Customer Description] : COUNTD ( [Event Description] ) }

               

              I wasn't sure what exactly should be counted: should it be [Event] or [Event Description] or something else?

               

              (I didn't carefully look at which dimensions affect the LOD (on the "Fixed" side of the colon). I just

              added all of the filters to context. Can be more meticulous about that next round).

               

              2)

              The LOD of was giving me a count of 13.

              I noticed that when I put [Event Description] on the Rows shelf of your Desired Count sheet, it added one more count to December.

              Please see sheet "Desired Count (2)" of the attached. Should it be 12 or 13?

               

              3)

              Unfortunately, trying to put this new total on your Merch Calendar broke your Row Grand Total.

              Please see sheet "Merch Calendar (2)".

              Might this be acceptable? Or would it be acceptible to put the grand totals on a separate sheet and put them adjacently in a dashboard?