I have a Project dim that each member is a Project (P01, P02...)
and Account dim that stores information of the each Project (Name, Start date, Finished Date...)
Finished Date member is in Date data type
So how can write a IF condition below in order to compare Project Finished Date with Current Year and Period members on FIX FIX (@Descendants(Projects), Descendants(All Year), Descendants("Year Total")...) IF (@CURRMBR(Period)->@CURRMBR(Year) < Project->FinishedDate) Do something... Else Do something
Please help me on this. Sorry for my bad grammar. Please ask if there is anything unclear
Thanks for quick response, Rahul. But what I need here is not comparing 2 Date data but comparing one Date data (Project->FinishedDate) with a intersection of current Year->Period member on FIX statement.
Essbase date calculation functions (@TODAY().@DATEDIF(),..) assume the date numbers are stored in seconds..but Essbase stores planning date datatype numbers in yyyymmdd format and they don't make any sense.
You can use functions like @Round, @Truncate, @Remainder and temp. variables in Essbase (VAR) to calculate the numbers of months difference b/w the two dates. These difference can then be compared in the conditions (<0 or >=0) for your logic.
Tks Ram, so is there anyway to convert current Member of Year and Period on FIX Statement to numeric or Date ? Or we all we can is
currYear = @Substring(@NAME(@CURRMBR("Year"), 2,3); // For FY13 return 13...
IF (@NAME(@CURRMBR("Period")) == "Jan") currMonth = 1;
ELSE IF (@NAME(@CURRMBR("Period")) == "Feb") currMonth = 2;
and so on
So the goal is to calculate the total number of months for each date on all possible lvl0 combinations. This will all be math logic. Alp from one of the previous links had a very good example for that.
Without knowing much about your case, I am just putting this together theoretically.
So the solution I'm following now is:
1. Use either Ram/Jake solution to convert Data data to Numeric/Comparable datatype
2. Use String Function, IF ... THAN convert current Year and Month member on FIX statement to Numeric/Comparable datatype
and do the compare these two converted value
Quite inconvenient, but it seem there's no better way