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 Candeo

      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


      Thank you

        • Re: How to show this kind of information day by day?
          Thomas 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:




            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.