Hi Cognoise,
I've built a logic (if then else construct) below in one of my cube based report to dynamically render last periods. The "Then()" and "Else()" logic resolves just fine (independently). However, the logic in its entirety resolves to an error because the "If()" part resolves to a string, it is expecting the "then()" and "else()" part also resolve to a string but not member or set of members. Kindly, please help to streamline the logic in If() condition.
if (
#timestampMask(_add_days ($current_timestamp, -1),'mm')# < #timestampMask($current_timestamp,'mm')#
-- if the day prior to current day month is less than current day's month
)
then (
lastPeriods (3,#'[Cube - XYZ].[Transaction Date].[Transaction Date].[Month]->:[PC].[@MEMBER].['+timestampMask(_add_days ($current_timestamp,-1),'yyyymm')+'01-'+timestampMask(_last_of_month (_add_days ($current_timestamp, -1)),'yyyymmdd')+']'#)
-- This resolves to months Nov, Dec and Jan
)
else (
lastPeriods (3,#'[Cube - XYZ].[Transaction Date].[Transaction Date].[Month]->:[PC].[@MEMBER].['+timestampMask($current_timestamp,'yyyymm')+'01-'+timestampMask(_last_of_month ($current_timestamp),'yyyymmdd')+']'#)
-- This resolves to months Dec, Jan and Feb
)
I have had issues in the past where to much is going on in the data item and the query fails.
Once I break down the data item into multiple with the solve order set it works.
Trying moving the "Then" and "Else" into two other data items i.e.
[DataItemIF]
If (blah) Then ([DataItem1]) Else ([Dataitem2])
Solver order would then be
[DataItemIF] {leave blank or set to 1, the following two have to be 1 more than this one}
[DataItem1] {set to one more than the parent i.e. 2}
[DataItem2] {set to one more than the parent i.e. 2}
Also by doing it this way you can copy [DataItem1] into another query and run tabular model to see if it works. Then do the same for [DataItem2]. Before copying them into the main query where they will reside.
I tried your way. It didn't work. It is still expecting the 'Then' clause to show single value instead of a set of members. :(
"However, the logic in its entirety resolves to an error because..."
if this is really the cause of the error, consider changing your if condition using dimensional functions.
The bottom line here is that OLAP queries cannot work with IF/THEN or CASE/WHEN statements. It just doesn't work.
But let's see what you're trying to do:
First you're always getting back the last 3 months
Then... if yesterday is in a previous month construct last month's mun, else construct this month's mun. This part is where I'm getting confused.
#'[Cube - XYZ].[Transaction Date].[Transaction Date].[Month]->:[PC].[@MEMBER].['+timestampMask(_add_days ($current_timestamp,-1),'yyyymm')+'01-'+timestampMask(_last_of_month (_add_days ($current_timestamp, -1)),'yyyymmdd')+']'#
should be all you need. If today is the first, you want it to go back and construct the previous month's mun. And that's what it does. But if today is the second, it will go back to yesterday (the first), and construct this month's mun.
Try just doing
lastPeriods(3,#'[Cube - XYZ].[Transaction Date].[Transaction Date].[Month]->:[PC].[@MEMBER].['+timestampMask(_add_days ($current_timestamp,-1),'yyyymm')+'01-'+timestampMask(_last_of_month (_add_days ($current_timestamp, -1)),'yyyymmdd')+']'#)