2 Replies Latest reply: Feb 25, 2013 2:15 PM by Jefferson Martins RSS

    How to show this kind of information day by day?

    Robson Luiz Cazetto Cand�o

      Hi, I have a really simple database with customers and daily amounts and I need to the show the information day by day aggregating the weekly amount summarizing till the end of the month.

       

      How can I show this information without creating 31 expressions for the days more the expressions summarizing weekly?

       

      Here is part of the layout that i Need. Considering showing just a month and remember that some months has less day than others.

       

      CustomerWeek 1Week 2
      01/jan02/jan03/jan04/jan05/jan06/jan07/janAmount08/jan09/jan10/jan11/jan12/jan13/jan14/janAmount
      John4050201223005608042
      Fred5266131078235902105141
      Taylor00100090191012067045

       

      Thank you

        • Re: How to show this kind of information day by day?
          Thom Mumaw

          Do you have the date as part of your detail?  If so, you could create a table with Year, Month, Week, Date and join based on date. Then just use either year, month, week or date as a dimension.  Then just use the SUM(xxxxxxx).  Try creating a master calendar something like this:

           

          Calendar_Temp_Table

          Load

            Num(Date#(Min(Period), 'YYYYMMDD'))  as MinDate,

            Num(Date#(Max(Period), 'YYYYMMDD)) as MaxDate

          Resident on your detail table.......

           

          Let vMinDate = Peek('MinDate', 0, 'Calendar_Range');                     //Don't forget to create your variables

          Let vMaxDate = Peek('MaxDate', 0, 'Calendar_Range');

           

          Drop table Calendar_Temp_Table

           

          [Master Calendar]:

           

          Load distinct

            Year(Temp_Date) * 100 + Month(Temp_Date) as period,

            Year(Temp_Date) as Year,

            Month(Temp_Date) as Month,

            Date(Temp_Date, 'YYYY-MM')  as YR_MTH,

            'Q' & Ceil(Month(Temp_Date) / 3) as Qtr

          ;

          Load Distinct

              MonthStart($(vMinDate) + IterNo()  - 1 as Temp_Date

          Autogenerate (1)

          While $(vMinDate) + IterNo()  - 1 <= $(vMaxDate);

           

          Let vMinDate = Null();

          Let vMaxDate = Null();

           

           

          Hope this helps.

          • Re: How to show this kind of information day by day?
            Jefferson Martins

            Hi rcandeo,

             

            You should do this using a pivot table with only one expression.

             

            See the attached file for more details. The excel file contais the data I used to create the example.

             

            Hope this helps you.

             

            Regards