5 Replies Latest reply: Jul 17, 2013 5:53 AM by Jonathan Dienst RSS

    Date Island Period as Chart Dimension

    Bruce Tedder

      Hi Guys

       

      I need to chart the SalesValue as a Line Chart using the Date Island’s DI_PERIOD (yyyyMM) is the x-axis dimension for this chart.

       

      I have tried this but my Sales graph is level all the way across.

      Sum({$<Date={"<=$(=Max(DI_Date))"},Type={‘I’}>} Value)   

       

      Type depicts an Invoice in my FACT table

       

      I used the Date Island's DI_Period (yyyyMM) as my x-axis dimension in the Chart.

        • Re: Date Island Period as Chart Dimension
          Adnan Rafiq

          Bruce,

           

          Apparently it seems that you have some data modeling problem.

          here are couple of tips: 1. Make sure table which has DI_Period is linked to the table which contains Value field.

          Apparently there is no association among these two table.

          2. if due to certain limitations you are not able to connect them, then create a field on monthyear(Date) from the date.

           

          Regards

            • Re: Date Island Period as Chart Dimension
              Bruce Tedder

              Hi Adnan

               

              Ok, so I have in the load statement for my FACT table done the following:

              LOAD

                   Date(QM_ADD_DATE,'yyyyMMdd')             as Date,

                   Date(QM_ADD_DATE,'yyyyMM')                as Period etc etc

               

              But when I use Period as my x-axis dimension, it leave gaps for each day.  I only need sales by Period, not Date.

               

              I cant seem to attach a image of the graph but hope you know what I mean?

              • Re: Date Island Period as Chart Dimension
                Bruce Tedder

                Hi Adnan

                 

                Ok, so I have in the load statement for my FACT table done the following:

                LOAD

                     Date(QM_ADD_DATE,'yyyyMMdd')             as Date,

                     Date(QM_ADD_DATE,'yyyyMM')                as Period etc etc

                 

                But when I use Period as my x-axis dimension, it leave gaps for each day.  I only need sales by Period, not Date.

                 

                I cant seem to attach a image of the graph but hope you know what I mean?

                  • Re: Date Island Period as Chart Dimension
                    Jonathan Dienst

                    Bruce,

                     

                    The Date() statement formats, but does not change the underlying value of the field. In your case, Date and Period contain exactly the same values, just formatted differently. So you have multiple (different) values for each period - one for each day. You can see that in a listbox and in your dimension.

                     

                    You could try something like this to get one value per period:

                     

                         Date(MonthStart(QM_ADD_DATE), 'yyyyMM') as Period

                     

                    Hope that helps

                    Jonathan

                • Re: Date Island Period as Chart Dimension
                  Jonathan Dienst

                  As far as your original post is concerned, you cannot use set analysis in reference to a dimension in a chart as the set analysis is applied once for the chart, before the dimensions are constructed. Is there a specific reason you are using a date island for the dimension?

                   

                  Jonathan