1 2 Previous Next 15 Replies Latest reply on Aug 22, 2016 7:33 AM by Sandeep Kotha

    Report on last week & Difference of a field based on last two weeks data

    Sandeep Kotha

      Hi All,

       

      I am trying to get the below two things in tableau.

       

      In my report,I have a field called "Fiscal Week" which consists of Week -1,Week -2,Week -3 and so on. My tableau is connected to SQL SERVER.So, every week I would get a new week number when I refresh.

       

      1. I created a summary report based on the last week data using some charts and so on.

      2.I created a new field called "Variation" using difference of the last two weeks data based on Revenue.Because every week "I want to see the variation of last two weeks".So, I have used difference in "Table Calculation" in "Revenue" field

       

      What I have done!

       

      Every week,I want the report based on the last one week only.Also, I want the field "Variation" which is calculated based on the last two weeks(as difference).

      I have used "Relative date concept" and I am getting the last two weeks data which I have used for "Variation" field calculation. But I want only the last week data in the report.

       

      My question is:

       

      How can I get one report(few charts) based on the last two weeks data and another report(some other few charts) based on the last one week data only in the same dashboard?

       

      Thank you in advance!

       

      Best Wishes,

      Sandeep

        • 1. Re: Report on last week & Difference of a field based on last two weeks data
          Andrew Watson

          There might be a number of options available to you, without a twbx I can't advise which is better.

           

          1. You could use a FIXED formula for the last 2 weeks (for the Variation field) - this should ignore the date filter

          2. You could use a parameter as your date filter instead of a relative date filter and drive calculated fields off that to act as filters

          3. It may be possible in your case to simply have different month filters on the different worksheets. If 'last week' is always shown on the dashboard no need to have a date filter on there

          1 of 1 people found this helpful
          • 2. Re: Report on last week & Difference of a field based on last two weeks data
            Sandeep Kotha

            Hi Andrew Watson,

             

            Thank you very much for the reply. I am using Tableau 9.2 version(.twb) But I do not know how to create field using FIXED formula for Variation field.I have week field consists of Week1,Week2,Week3 and so on.How do I want the variation field is that:

            Example : Suppose Week 6 is the latest Week, then Week6(Sales)- Week5(Sales) .Could you please elaborate with an example what you have said?

             

             

             

            I just used the above difference option and i did not create any calculated field.

            I have used Relative date but when the data is refreshed, the latest week is not checked on Filter option. Please let me know your inputs

            I really appreciate for your response.

             

             

            Thank you.

             

            Best Wishes,

            Sandeep

            1 of 1 people found this helpful
            • 3. Re: Report on last week & Difference of a field based on last two weeks data
              Andrew Watson

              If you're using a relative filter for last week then you are removing anything that is not last week from the data - so you won't be able to do a week on week comparison using that method.

               

              You have a number of options.

               

              1. You're connecting to a SQL Server back end so one possibility is to write some SQL only pulling in the exact data you need, with the calculations already carried out in SQL, meaning you won't need filters in the Tableau front end

               

              2. Have a filter 'behind the scenes' restricting to the last 2 weeks. Create calculated fields for this week to return the sales. How to do this depends very much on your data structure. For example you could use a FIXED to get the latest week (you'll have to alter this to reflect changes in the year and to your data structure) {FIXED:MAX([Week])}

               

              This week sales could be: IF [Week] = {FIXED:MAX([Week])} THEN [Sales] END

               

              Last week sales could be IF [Week] + 1 = {FIXED:MAX([Week])} THEN [Sales] END

               

              3. The FIXED isn't necessary, it can also be done using date functions, again it depends on your underlying data structure.

               

              This weeks sales could be: IF [Week] = DATEPART('week',TODAY()) THEN [Sales] END

               

              Last weeks sales could be: IF [Week] = DATEPART('week',DATEADD('week',-1,TODAY())) THEN [Sales] END

              1 of 1 people found this helpful
              • 4. Re: Report on last week & Difference of a field based on last two weeks data
                Sandeep Kotha

                Hi Andrew Watson,

                 

                Thank you very much for the reply. I appreciate for your reply.I am reffering to Point 2 as what you suggested.

                 

                1) I am able to create a field “This week“  using fixed option and it worked perfectly. But When I use the below fixed command for creating the last week

                if [Week] + 1={fixed:MAX([Fiscal Week])} then Sales END

                It is giving error as :

                Can’t add string and Integer values

                 

                Also, I am creating a field “This week“  using date options as well as shown below.

                 

                If Week=DATEPART('Week', today()) Then Sales END

                 

                But it is giving the below error:

                Argument ‘Week’ to datepart must specify a datepart(‘year’,’ month’, etc.).

                 

                Could you please let me know how to resolve this error?

                 

                Also, both the "Last week" and "This week" worked perfectly using the below command:

                "This week" :  If Date={fixed:MAX(Date)} then Sales else 0 END

                "Last week" :If Date + 7 ={fixed:MAX(Date)} then Sales else 0 END

                 

                2) Now the problem is with the field called Variance(This Week- last Week). I have created a calculated field variance as "This Week"- "last Week" but when I use this in rows shelf or columns shelf, I got blank instead of  a value. Is my formula for variance correct? If not, please guide me with the method to find the difference of "This Week"- "last Week".

                 

                Thank you very much for all your help on this.

                 

                 

                Best Wishes,

                Sandeep

                 

                1 of 1 people found this helpful
                • 5. Re: Report on last week & Difference of a field based on last two weeks data
                  Andrew Watson

                  OK, a number of questions at the same time here.

                   

                  "I have created a calculated field variance as "This Week"- "last Week" but when I use this in rows shelf or columns shelf, I got blank instead of  a value."

                   

                  Try SUM([This Week]) - SUM([Last Week])

                   

                  "If Week=DATEPART('Week', today()) Then Sales END

                   

                  But it is giving the below error:

                  Argument ‘Week’ to datepart must specify a datepart(‘year’,’ month’, etc.)."

                   

                  I can't give you exact formulas as I don't have your data, I can only guess what might work:

                   

                  If DATEPART('week',[Date])=DATEPART('week', today()) Then [Sales] END

                   

                  "if [Week] + 1={fixed:MAX([Fiscal Week])} then Sales END

                  It is giving error as :

                  Can’t add string and Integer values"

                   

                  I assume your [Week] field (if it exists) is a string - it would need to be converted to an integer to add one. Alternatively, if this Week field is actually a date then use DATEADD to add a week

                  1 of 1 people found this helpful
                  • 6. Re: Report on last week & Difference of a field based on last two weeks data
                    Sandeep Kotha

                    Hi Andrew Watson,

                     

                    Thank you so much for your answers. SUM([This Week]) - SUM([Last Week]) has worked perfectly. I have got last week and this week and variance data.

                    Now, below are the challenges for me:

                    1) Every week when I refresh the data, i would get the this week data and the last week data[This week] and  [Last Week]. Now how do I change the field names automatically.

                    Example 1: Assume this week is Week 8, so when I refresh the data. I want the fled names for "This week" to be "Week 8 " and "Last week" to be "Week 7".

                    Example 2: Assume next week as Week 9, so when I refresh the data. I want the fled names for "This week" to be "Week 8 " and "Last week" to be "Week 7"  and so on.

                    The reason to ask the field names to change is that users should know what week data is showing on the dashboard,right?

                     

                    2) When I take running total of average(numeric field), then I am not getting the correct grand column total value.

                     

                    Example:

                    21

                    32

                    54

                    Column Total should be  21+ 32+ 54= 107 But it is giving a different value. This is happening only when I use "running total of average(numeric field)". I am getting the correct grand column total value when I use "Running total of sum(numeric field)".

                     

                    It would be great If you can help me on this.

                    Thank you.

                     

                    Best Wishes,

                    Sandeep

                    1 of 1 people found this helpful
                    • 7. Re: Report on last week & Difference of a field based on last two weeks data
                      Andrew Watson

                      You're mixing threads - I'll answer question 1, question 2 sounds more complex so I suggest you close out this thread by marking one of the answers as correct, then open a new thread, more people wil get to see it that way.

                       

                      For question 1 I would create a calculated field to calculate the latest week, FIXED is a good way to do that: {FIXED: MAX([WeekField])}

                       

                      Do a dateadd or minus 1 from the weekfield (whatever ever works for you) to get last week.

                       

                      I suggest you put these in the title of your chart/table so it's clear to the user what this week number is.

                      1 of 1 people found this helpful
                      • 8. Re: Report on last week & Difference of a field based on last two weeks data
                        Sandeep Kotha

                        Sure will do the same. Thank you once gaian. It helped me a lot for my work.

                         

                        I am very much happy with your answers.

                         

                        Best Wishes,

                        Sandeep

                        1 of 1 people found this helpful
                        • 9. Re: Report on last week & Difference of a field based on last two weeks data
                          Sandeep Kotha

                          Hi Andrew Watson,

                           

                          I still did not get the Week field names. This is regarding the Field label names.I have done what you have said.

                           

                          For "This Week" ,I have used the below code

                          { FIXED :MAX([Fiscal Week])}

                           

                          Whenever there is new week comes, the field name should get change accordingly.

                           

                           

                           

                          Could you please help me on this? or if You want me to start a new discussion, I can do that.

                           

                           

                          Best Wishes,

                          Sandeep

                          • 10. Re: Report on last week & Difference of a field based on last two weeks data
                            Andrew Watson

                            Drag your 'This Week' field to the Detail shelf. You'll then be able to add it to the Title. Then the title wil no longer be Sheet3, you can write something like "This week is week number [ThisWeekField]"

                            • 11. Re: Report on last week & Difference of a field based on last two weeks data
                              Sandeep Kotha

                              I have done the same thing as said. I dragged "label 1" onto detail shelf. Label 1 has the formula : { FIXED :MAX([Week])}

                               

                              Title did not changed automatically.Sorry, I might have missed something.

                               

                               

                              My question is that: Whenever there is a data refresh, The field names of  "L Week" and "T Week" should be replaced with that particular "week" names Like Week -7 and Week 8 and so on. This should happen automatically.

                               

                              Please let me know If I miss anything in the method what you have said.

                               

                              Thank you.

                               

                              Best Wishes,

                              Sandeep

                              • 12. Re: Report on last week & Difference of a field based on last two weeks data
                                Andrew Watson

                                You need to edit the title and bring Label 1 into it. You won't be able to change the field headers, Tableau doens't offer that flexibility, hence why I recommend using the title to convey the information.

                                1 of 1 people found this helpful
                                • 13. Re: Report on last week & Difference of a field based on last two weeks data
                                  Sandeep Kotha

                                  Perfect! I got it now. I was not able to place label directly inside the title. Using Insert option , I could do that.

                                  1) I hope this Week number will keep changing as on data is refreshed and new week number would come,right?

                                   

                                  2) I have considered "L Week" and "T Week" based on max(Date) and will have 1-52 weeks in Week field in 2017 year, When we get first week of next year 2018. Does  "T Week"  give the values of Week1 of 2018 or not? Because every year,it should come from Week 1- Week 52 and again Week-1 and so on.

                                   

                                  Thank you.

                                   

                                  Best Wishes,

                                  Sandeep

                                  • 14. Re: Report on last week & Difference of a field based on last two weeks data
                                    Andrew Watson

                                    1. Correct, the week number will change based on your underlying data

                                    2. It should for This Week. However for last week you should look at the DATEADD function rather than minussing days, as you're currently doing

                                    1 of 1 people found this helpful
                                    1 2 Previous Next