Dimension by Date

[#1]

I am trying to show demand for an item with weekstart as my dimension.  Each item has a date that I convert to the weekstart of that date.  I use week as my dimension and sum the quantity as my expression.  However I need to show ALL weeks for a 10 week period on the chart even if there is no data for some of those weeks.

I tried creating a temp week table that autogenerates all weekstarts for 10 weeks where tempweek = week but that does not work.

Here is a sample of data for part A and a screenshot of my chart.  Any ideas?

Item	Date	Week	Quantity
A	3/8/12	3/5/12	4
A	4/5/12	4/2/12	4
A	5/4/12	4/30/12	5

[swuehl]

How have you created the tempweek in your chart? And have you unchecked 'suppress zero values' in presentation tab?

One approach might be to create a master calendar (search for master calendar here in the forum, there are tons of threads about this topic, including sample files), then limit the amount of weeks shown to the required ten weeks by either a calculated dimension or set analysis.

[jonathandienst · Accepted solution]

Chris

You probably need a date island containing the 10 week period. You would use the date island field as the dimension and sumif expressions for the quantities. If you turn off suppress zeroes, you should get all the weeks whether there is data or not.

There are quite a few posts in this and similar issues - search for date or data island and "asof dates"

Regards

Jonathan

[#4]

I think you are on the right track,you need a temp table that contains your 10 week.

you should be able to use the week function to return the weeknumber for the date (and convert it into the right week within the 10 week period).Join that value with your created table.

[#5]

I was not doing a sumif I was doing an if...sum.., that was my problem.  Works now, thanks for you help guys!