Using an Attribute as an Element in a cube

[Danielle]

Hi ,

I am quite new to TM1 and am trying to build a cube that spreads a monthly salary across the month.
Our Pay cycle is the 15th of Mth 1 to 14th of Mth 2
I currently have built a cube that looks like this (just a rough extract):

Date	Cost Centre	Salary $	Daily Salary	Salary per day	Business Day
15/01/2015	Sales	$10,000	$455	$455	0
16/01/2015	Sales	$-	$-	$455	0
17/01/2015	Sales	$-	$-	$455	0
18/01/2015	Sales	$-	$-	$455	0
19/01/2015	Sales	$-	$-	$455	0
20/01/2015	Sales	$-	$-	$455	0
21/01/2015	Sales	$-	$-	$455	0
22/01/2015	Sales	$-	$-	$455	0

What I want it to look like is something like below:

Date	Cost Centre	Salary $	Daily Salary	Salary per day	Business Day
15/01/2015	Sales	$10,000	$455	$455	1
16/01/2015	Sales	$-	$-	$455	1
17/01/2015	Sales	$-	$-	$0	0
18/01/2015	Sales	$-	$-	$0	0
19/01/2015	Sales	$-	$-	$455	1
20/01/2015	Sales	$-	$-	$455	1
21/01/2015	Sales	$-	$-	$455	1
22/01/2015	Sales	$-	$-	$455	1

The details for Salary by Day is currently

Code: Select all

=LASTNZ('Daily Salary')

What I want to do is have it be

Code: Select all

=LASTNZ('Daily Salary')* Business Days

And Business days to pick up an Attribute from the Date dimension.
This is where I’m striking problems. I have tried to use an ATTRS function to populate the Business Days element. ( the last 4 columns shown are set up as elements of a calculation dimension)
So far I have tried to use

Code: Select all

=attrs(theDate, !thedate, BusDay) 

where !thedate is the date for each record shown.

What I’m looking to do is to set the element in the calculation to pick up the value for each row automatically?
Can anyone please advise how I do this?
Thanks in Advance
Danielle

[gtonkin]

Hi Danielle,
I would normally start with a Calendar cube but could be an attribute on the time dimension - depending on your requirements you may have a measures dimension with Calendar Days and Business Days with 0/1.
Your time dimension would usually be days, consolidated into months (15th to 14th )then Quarters/Halves/Years etc.
In the salary cube, assuming you would have a staff dimension too (unless Cost Centre is it), you can then calculate the value for each days as a percentage of the month e.g.
If we were looking at 2015/02/02 which was a Monday and February (2015-02) consolidated to 20 Business Days then I can use the ratio of 1 to 20 times by the monthly salary.
Something like DB('Calendar',!Period,'Business Day')\DB('Calendar',ELPAR('Period',!Period,1),'Business Day') * DB('Staff'....)
This also caters for allowing 0.5 days if Saturdays are worked.

The gotchas are where to store the monthly Salary - need to be against the time dimension as it will change over time - some people create a zero day element to store this or any carry over information from previous months. Some use an additional layer to split input/import/rule derived data etc.

Don't forget to feed your result - the trick here is if you feed from the Calendar you will need to feed ALL staff. If you feed from wherever you place Salary, you will end up feeding non-business days. Depending on the complexity, no. of elements, there are pro's and con's with these feeders.


Good luck.

[Danielle]

Thanks for the advice!

I found a solution to this. but as wth everything new, now have a different problem.

But i will put that in a different post.

Thanks Again

Danielle