How to show this kind of information day by day?

[rcandeo]

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.

Customer	Week 1								Week 2
	01/jan	02/jan	03/jan	04/jan	05/jan	06/jan	07/jan	Amount	08/jan	09/jan	10/jan	11/jan	12/jan	13/jan	14/jan	Amount
John	4	0	5	0	2	0	12	23	0	0	5	6	0	8	0	42
Fred	5	2	66	1	3	1	0	78	23	5	9	0	21	0	5	141
Taylor	0	0	10	0	0	9	0	19	1	0	12	0	6	7	0	45

Thank you

[tmumaw]

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.

[jeffmartins · Accepted solution]

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