4 Replies Latest reply: Mar 8, 2012 11:51 AM by Chris Cahoon RSS

    Dimension by Date

      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?

       

      ItemDateWeekQuantity
      A3/8/123/5/124
      A4/5/124/2/124
      A5/4/124/30/125

       

       

      demand_issue_qv.bmp

        • Dimension by Date
          Stefan Wühl

          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.

          • Dimension by Date
            Jonathan Dienst

            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

            • Dimension by Date
              Johan Jonsson

              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.