measure by day

[master_student]

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

[swuehl]

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]));

[master_student]

Thanks Swuel for your response but the budget sum is 44500 instead of 500. see below:

[swuehl]

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]));

[master_student]

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.

[swuehl]

If you join the tables, this will duplicate your facts. Any reason why you want to join your tables?

[master_student]

the reason is to get the calendar date from the calendar dimension.

please let me know if you have other solution.

Thank you

[master_student]

Any help please.

[swuehl]

Can you tell what's wrong with my previous sample, where I linked the calendar to your fact table?

[master_student]

could you please use my calendar dimension.