You have not attached your wb...
I know, was hoping it was an easy answer and did not require workbook upload.
Can do the upload tomorrow if required.
As I see your lookup is set with 0, and you get the same value, just zn(sum) will be ok
On the question how to show weeks without data, I think you have to build another data source and blend on weeks/dates
I attached a wb (9.3) based on Superstore data where in case of 'Machines' Subcategory sales did not occur in all weeks:
Sheet 1 is built the easy way and it is gonna work provided you do not filter weeks within and prior to the [last x weeks] range as the 'show missing values depends on that range (i.e. you must have the start and end dates). Here are the last 5 weeks displayed:
Sheet 2 is much more complicated.... but it does not depend on full date range within your data source and it can be arbitrary. The filter is set to Top [last X weeks] of [custom week]:
last x if missing.twbx 464.8 KB
Firstly thank you so much for taking the time the create the above sample.
I am trying to replicate both Sheet 1 and 2 in my workbook, and stuck on both sheets.
Questions (if you have time):
1. For Sheet 1, I have almost replicated what you have done, how do you create the last()<[Last X weeks]:True filter. I can not figure out how?
2. For Sheet 2, tried to replicate your uberness and failed miserably, Click here to see my replication attempt of Sheet 2 (please don't laugh too hard ). I am not sure why I only get the min and max weeks and not all weeks in-between. What am I doing wrong?
That filter I created in details shelf first and then dropped into filters:
But you may just create a named calculation instead.
I've attached a modified twbx where you are free to choose whether your Weeks Range ends on:
- max of date in data source
- arbitrary date selected with another parameter
It is rather complicated technique and I do not have time now to explain it in details. I encourage you to examine it yourself in details paying attentions to things like:
- showing missing values (dates or bins only) - check out domain padding podcast in the great TDT!
- addressing and partitioning of (nested) table calculations
- filter types used and conditions (Top X)
- stacked marks
last x if missing.twbx 464.7 KB