3 Replies Latest reply on Nov 17, 2016 6:02 AM by Mark Holtz

    Create calculated parameter to convert days to week dynamically

    Cindy Cao

      Hi there

      I'm new to Tableau and still having trouble with calculations and parameters.


      I'm trying align my data to see how the number of data collected change week by week.

      On the left I have rows in "yyww" format but on top I have it in count of days.

      I'm trying to convert the days to week in "ww" format so it's week by week comparison.

       

      The logic is simple: day 0-6 is week 1, day 7-13 is week 2.

      The challenge is that "Day count" is in Dimension not Measure, if it's changed to be Measure then the format will not be the same as below.

      Is there a way to change the "Day count" to week and dynamically add more as days increases based on data collected but still keep the same format?

       

      Screen Shot 2016-11-16 at 1.23.07 PM.png

      Any help is much appreciated!

        • 1. Re: Create calculated parameter to convert days to week dynamically
          David Li

          Hi Cindy, is this what you're looking for?

          If so, you can get this by right-clicking the [Day count] field and clicking Create > Bins. Set the bin size to 7. That will create a new field called [Day count (bin)] that you can put in the rows shelf.

          • 2. Re: Create calculated parameter to convert days to week dynamically
            Cindy Cao

            Hi David!

            Great solution!

             

            It'd be perfect if I can name it with the number for week for example:

            day -> week

            0 -> 1

            7 -> 2

            14 -> 3

             

            Is this possible?

            • 3. Re: Create calculated parameter to convert days to week dynamically
              Mark Holtz

              Hi Cindy,

               

              As usual, there are more than one way to do what you want.

              David was using the "Create Bins" function in Tableau. You can assign an alias for each value within the [Day count (bin)] field. However, you'd have to do this manually for every new tier in the bin.

               

              I prefer to just build calculated fields to do all my grouping and binning.

              You can be as tricky as you want to be with a calculated field.

               

              Option A is to statically define your buckets and your outcome with an IF statement--basically identical to the Create Bins option. But any [Day count] values you don't account for in the formula will result in NULL in this grouping calculation.

               

              Option B is to use the MODULO function ([A]%[B] = the integer remainder of A divided by B) to create your buckets AND name them as you desire.

               

              //IF [Day count] < 7 THEN 1

              //ELSEIF [Day count] < 14 THEN 2

              //ELSEIF [Day count] < 21 THEN 3

              //ELSEIF [Day count] < 28 THEN 4

              //but you have to keep adding the next age bucket...

              //END

               

              //so use MODULO function...

               

              //days divisible by 7

              IF [Day count]%7 = 0 THEN ([Day count] / 7)+1

               

              //days not divisible by 7

              ELSE ((7-([Day count]%7)+[Day count])-7)/7+1

              END