5 Replies Latest reply on Mar 28, 2013 7:28 AM by Tom Barnes

    Calculated field using a dynamic value

    Tom Barnes

      So newbie here.  Have been working with Tableau for a couple of weeks.  This is my first time trying out the forum.  So here is what I am trying to do.  I have a field with week numbers formatted 201101, 201102 etc.  What I need to do is create a YTD Sales field.  So I grab the correct year which is easy enough but I also need to find the most recent week of data and have it pull the weeks for the year up to the newest week for LY and TY.

       

      So this is what I thought would work but apparently something about aggregate and non aggregate functions not working.

       

      if left(STR([Week]),4) = "2012" AND right(STR([Week]),2) <= MAX([Week]) THEN

      [Sales]

      End

       

      So basically LY is 2012 so I am pulling that and it works.  However, I can't compare the week with the last week of data in 2013.  The last week of data is 201307.  So if I put in "07" instead of MAX([Week]) it works great.  But what happens when I update the data next Monday with 201308 data in it?

       

      If parameters were dynamic I could use that in a heartbeat but apparently even in 8.0 that isn't the case.

       

      Any ideas?  Thank you in advance!

        • 1. Re: Calculated field using a dynamic value
          Jim Wahl

          Hi Tom,

           

          There are probably multiple ways to solve your problem, but I think the below--based on table calculations--should work well in different views.

           

          1. Make sure the raw date [Date] "201101" is in the dimension category.

          Generally, dates should be dimensions, but this is also necessary to specify [Date] as a partition for the WINDOW function below to find max week.

           

          2. Create [Date Year] and [Date Week] fields and put in hierarchy.

          I left them as strings and used Tableau's string functions RIGHT, LEFT, .. This step may not be strictly necessary, but I think it's useful and may simplify your view creation.

           

          3. Create table calc for [Week Max].

          [Week Max] = INT(RIGHT(STR(WINDOW_MAX(MAX([Date]))), 2))

           

          This finds the maximum date and then extracts the week number. I convert this as an INT so that I can use it in the YTD field below.

           

          The key to table calculations is the Compute Using option which specifies the partition that the expression is computed over. We want [Week Max] to be computed over all dates in the data. And if you add additional dimensions, such as Date Year or Date Week, you may need to revise the table calc > Compute using advanced > and add all of these dimensions to the right-hand side. This can be confusing, but the "Description" Tableau provides on the Edit Table Calc screen is helpful.

           

          4. Create table calc for [YTD Sales]

          IF FIRST() == 0 THEN

          WINDOW_SUM(

             IF INT(RIGHT(STR(ATTR([Date])), 2)) <= [Week Max]

             THEN SUM([Sales])

             END)

          END

           

          Starting inside, you can see the core SUMIF type function. The ATTR() function is an aggregate, but returns only a single value or * if there are multiple values. WINDOW_SUM sums these values, and the FIRST() == 0 means that the value is returned only for first row in the partition --- this prevents the overprinting you often see.

           

          Again, the compute using is key. Here we want to compute using [Date Year] and [Date] (in that order), resetting every [Date Year].

           

          5. Creating the view.

          Add [Date Year] to the rows shelf. Add [Date] to level of detail. Add [YTD Sales] to the Text shelf. Since [YTD Sales] also includes [Week Max] this is called a "nested table calc." Note, however, you don't need to explicitly include [Max Week] in the view.

           

          You'll probably see only one value in the table. This is due to the compute using not being set. Right-click on [YTD Sales] > Edit Table Calculation. In the "Calculated Field" Box, you'll see a pull-down menu with [YTD Sales] and [Week Max].

           

          Select [Week Max] > Compute using advanced > and move all of the date fields to the right-hand Compute Using side.

           

          Select [YTD Sales] > Compute using advanced > and verify [Date Year] and [Date] or on the right-hand side in that order. Click OK, then select "Restarting every" Date Year.

           

          Now you should have:

          ytd_sales.png

           

          Phew! Again, there may be other ways of doing this and if you have a more specific view in mind, feel free to post it and see if someone else comes up with a better solution. But table calculations are critical to using Tableau, and this is a nice example of the importance of the Compute using aspect of table calcs.

           

          Jim

          • 2. Re: Calculated field using a dynamic value
            Tom Barnes

            Jim - Thank you for your response and sorry I haven't got back with you quicker.  I was out of the office yesterday so only able to try your solution today.

             

            From looking at your workbook it seems to work exactly how I want it to work. However, for the life of me, I cannot recreate your success.  I grabbed a section of my data and randomized the items and numbers so as not to disclose any real data but the fields are all the same and I tried to recreate using your directions and as far as I can tell the two workbooks are identical ------ BUT mine isn't working properly!!!  It keeps coming up with a simple sum for the entire year of 2012 and 2011 instead of just the year to date data like you have created.

             

            Could you please look at my workbook and tell me what I am missing here?  If I had hair I would have pulled it out all over again this morning.

            • 3. Re: Calculated field using a dynamic value
              Jim Wahl

              Hi Tom,

               

              Right-click on [YTD Sales] > Edit table calc. Because this is a nested table calc, in the top of this dialog box, you'll see a pull-down for "Calculated Field." Select Week Max. Now you'll see the Compute using is set to Date, which means the table calc is operating on the Date dimension for each of the other dimensions in the view, in this case the Date Year dimension. So you're finding the Week Max for each year. This is why you're getting YTD total based on the max week in each year.

               

              To fix this, in the compute using box, select Advanced and move Date Year over to the right-hand "Addressing" side. Nothing should be on the left-hand "Partitioning" side. Now you'll find the Max Week for all dimensions in the view.

               

              As a side note, I notice the [YTD Sales] is computed over all dimensions, but is reset every Date Year. You could get the same result by just computing over "Date," which means all other dimensions will be used for partitioning. Since there is only one other partition in this view -- Date Year -- it was the same effect as putting all dimensions in the "Addressing" side and resetting every year.

               

              Hope this is clear(er)!.

               

              Jim

              1 of 1 people found this helpful
              • 4. Re: Calculated field using a dynamic value
                Tom Barnes

                You are awesome, I knew there was something easy that I was just missing - and sure enough I was!  Thank you so much for your help.  This is exactly what I needed.  I think I need to go learn more about partitions and table calculations. My understanding is fuzzy at best.  Thanks again!

                • 5. Re: Calculated field using a dynamic value
                  Tom Barnes

                  Jim,

                   

                  If you feel so inclined I have another question on this project.  I posted the question here:

                   

                  http://community.tableau.com/thread/124780

                   

                  Thank you for all of your help.