5 Replies Latest reply on Apr 23, 2013 11:59 AM by arthi.keat

    Modify Total

    arthi.keat

      Hi,

       

      Can you please help me manipulate the grand total. Please find the attached packaged file. I tried to solve one problem and the solution to that problem led to another problem.

       

      First problem was to display all weekdays even though it was not in data.

       

      What I needed to do is to display all default weekdays even though it is not in data. I tried to do that but could not figure a way out so what I did was to introduce false product id counts for all weekdays. So that makes the total one more than the actual. That gave rise to my second problem. The grand total is also more by 7 now.

       

      I know we can try to do it in a dashboard with all weekdays in one sheet and total in another sheet have the total and combine them. But, the reason I would not like to do that as the scale will not be the same and I will have to manually fix the scale which might not be the correct thing to do.

       

      Can you please help. I need to get the correct grand total and show all weekdays in one sheet.

       

      Also, this is one step ahead. If we get this solved then I would like to make it automated. If you can please have a look at the dashboard. I would like to automatically show prev week and current week numbers. How can i achieve that.

       

      Can you please help me.

       

      Thanks,

      Arthi.

        • 1. Re: Modify Total
          Shawn Wallwork

          Arthi, I haven't opened your workbook, but the Grand Total go-to guy is Jonathan. So let me post the links to his blog, and then if you still need help you can ask him directly:

           

          http://drawingwithnumbers.artisart.org/

           

          Then type Grand Total in the search box.

           

          --Shawn

          • 2. Re: Modify Total
            arthi.keat

            Thanks Shawn.

             

            I have did look around for a solution which would be helpful to my problem.. but was not able to find it. May be I did not look hard enough.

             

            I will request Jonathan to help me with this when he has a moment.

             

            Thanks for your response..

            • 3. Re: Modify Total
              Shawn Wallwork

              Arthi, this post will ping Jonathan and make him aware of your post. Hope you get your answer.

               

              --Shawn

               

              PS: I'm always awed by anyone participating in a forum using a non-native language. I really don't think I could do it.

              • 4. Re: Modify Total
                Jonathan Drummey

                Hi Arthi,

                 

                See the attached for an example set up in Superstore Sales, for the grand total I used the technique outlined in http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/. The harder part was getting just the total for prior week with the weekday values and total for current week to display in the same worksheet. I ended up using custom field that was then manually hidden, notes are in the attached.

                 

                I was not able to do this in your data because I don't understand the date algorithm you are using, here are some issues you will have to deal with:

                 

                - While there are techniques to pad the data in Tableau using Show Missing Values, they are limited based on the ranges of the source data and the pills in the view, and in your case I think you are better off doing the padding yourself. The one change to make is to set the productID to Null for the additional padded rows. That way the COUNTD(productID) won't count those rows and you won't have to alter your calcs to deal with them.

                 

                - What defines the boundaries of current week vs. previous week? Are you looking at prior 7 days from today and then the prior from that, the prior 7 days from the latest date in the data, a Monday through Sunday boundary, or what? My suggestion is to generate a date and the appropriate week number from the data for each row. Then you can use those in place of the WEEKDAY(Order Date) and custom Weekday and Week Header calcs that I'd set up in the attached.

                 

                - I didn't set up the calculations to deal with the change in year at January 1 (or if you have a different year start). You'll have to adjust your calcs to deal with this.

                 

                Hopefully this is enough to get you started, if you need more help let me know!

                 

                Jonathan

                • 5. Re: Modify Total
                  arthi.keat

                  Thanks for the reply Jonathan. Really appreciate your help and your time.

                   

                  I will review your workbook and ping you again if I need any help. Hope that is okay.

                   

                  Thanks again for your help..

                   

                  Best Regards,

                  Arthi.