Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello everyone,
Hope this finds you well.
I have an excel file that contains the budget by department and by month of date as you can see bellow :
Departement | month of date | budget |
---|---|---|
finance | 01/01/2016 | 500 |
Information system | 01/02/2016 | 1000 |
My objective is to display the budget by day and the sum of budget still the same, like that :
Departement | calendar date | sum (budget) : 500 |
---|---|---|
finance | 01/01/2016 | 500 |
finance | 02/01/2016 | 500 |
finance | 02/01/2016 ......UNTIL 30/01/2016 | 500 |
could you please help me?
Thanks in advance
You can do this using a WHILE clause like
SET DateFormat = 'DD/MM/YYYY';
LOAD Departement, Date([month of date]+iterno()-1) as Date, budget INLINE [
Departement, month of date, budget
finance, 01/01/2016, 500
Information system, 01/02/2016, 1000
]
WHILE iterno() <=Day(Monthend([month of date]));
Thanks Swuel for your response but the budget sum is 44500 instead of 500. see below:
Ok, I misunderstood your request.
you can create a separate calendar table, so you are not duplicating your budget values.
SET DateFormat = 'DD/MM/YYYY';
INPUT:
LOAD * INLINE [
Departement, month of date, budget
finance, 01/01/2016, 500
Information system, 01/02/2016, 1000
];
Calendar:
LOAD DISTINCT
[month of date], Date([month of date]+iterno()-1) as Date
RESIDENT INPUT
WHILE iterno() <=Day(Monthend([month of date]));
Thanks Swuehl.
I have a calendar dimension :
month start date : 01/02/2016
calendar date : 02/02/2016 ...29/02/2016
so I did :
INPUT:
LOAD * INLINE [
Departement, month of date, budget
finance, 01/01/2016, 500
Information system, 01/02/2016, 1000
];
left join
LOAD DISTINCT
[Month start date] as [month of date]
,[%Date ID]
RESIDENT D_Calendar;
but the sum budget is 45500 instead of 500.
Thanks in advance for your help.
If you join the tables, this will duplicate your facts. Any reason why you want to join your tables?
the reason is to get the calendar date from the calendar dimension.
please let me know if you have other solution.
Thank you
Any help please.
Can you tell what's wrong with my previous sample, where I linked the calendar to your fact table?
could you please use my calendar dimension.