cancel
Showing results for 
Search instead for 
Did you mean: 

Comparing Dimension attribute with Date

amrita_goswami
Participant
0 Kudos
262

Hi All,

I am using an Advanced formula to calculate Salaries for Employees. Start and End Month are maintained as attributes of Version.

Now there are some Employees whose Hire Date is later than the Version Satrt Month. For example, Budget Start Month = 202301 while employee Hire date = 202304. So the salary for this employee should be calculated from April onwards.

To achieve this, while looping through the dates I need something like :

IF [d/Employee_ID].[p/HireMonth] <= [d/Date] THEN
//Do calculation
Endif

However this syntax is not allowed. If i try to use the Memberset property then also the syntax is not accepted.

MEMBERSET [d/Date] = [d/Version].[p/StartMonth] TO [d/Version].[p/EndMonth]

Please suggest if there is any alternate way to compare an attribute value with date.

Thanks,

Amrita

View Entire Topic
N1kh1l
Active Contributor
0 Kudos

amrita_g_pwc

You have to use DATEDIFF to build the logic. Something like below to check if employee hire date is less than version end date

IF DATEDIFF([d/Employee_ID].[p/HireMonth],[d/Version].[p/EndMonth], "MONTH") >= 0 THEN

DATA()

ENDIF

OR

MEMBERSET [d/Date] = [d/Version].[p/StartMonth] TO [d/Version].[p/EndMonth]


IF DATEDIFF([d/Employee_ID].[p/HireMonth], [d/Date], "MONTH") >= 0
DATE()
ENDIF

The above are just checking that Employee hire date falls within Budget start and end date

Hope this helps

Please upvote/accept if this helps you.

Nikhil