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..
Arthi, this post will ping Jonathan and make him aware of your post. Hope you get your answer.
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.
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!
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..