Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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