Range List Using Two Date Hierarchies
Hello, I don't think this is possible but thought it's worth a shot asking the question!
I have a discovery with filters for TransDate and ClosedDate. The aim is to see open transactions at the end of any given month, so if I were looking at last month I would filter TransDate full month name <= Feb 2020 and ClosedDate full month name = [blank] or > Feb 2020.
I created a parameter for TransDate and created a range list like this:
{Range(OpeningPeriod([AssetTrans].[TransDate full month name]) , [AssetTrans].[TransDate full month name].@[a0268f77-21f1-4005-918e-a73c97469204])}
That works great, and shows me a range from the first month in the TransDate to the value selected in the parameter- now I am wondering if it's possible to do something similar with the ClosedDate, but using the TransDate parameter... so something like:
{Range(NextMember([AssetTrans].[TransDate full month name].@[a0268f77-21f1-4005-918e-a73c97469204]), ClosingPeriod([AssetTrans].[TransDate full month name]))}
I tried that and it didn't give me any error when I tested it, but I was not getting the expected result (I just got the closing period from ClosedDate) wondered if I may be doing something wrong or if it really is not possible!
Thank you :)
6 replies
-
Hi Sophie durrant ,
If I understand correctly, the second set you are trying to create is under the "ClosedDate" column, right?So first comment is that the ClosingPeriod needs to be set on that column, not on the TransDate column.
Regarding the parameter - it cannot be used directly on a different column than it belongs to.
However, assuming that the 2 columns have the same elements, you can use the parameter to create an element on the ClosedDate column like this:
StrToMember([AssetTrans].[ClosedDate full month name], [AssetTrans].[TransDate full month name].@[a0268f77-21f1-4005-918e-a73c97469204].caption)
The syntax will take the caption of the month that you selected in the parameter and create a selection of that month in the ClosedDate column.
So the full syntax would something like this:
Range( NextMember( StrToMember([AssetTrans].[ClosedDate full month name], [AssetTrans].[TransDate full month name].@[a0268f77-21f1-4005-918e-a73c97469204].caption) ), ClosingPeriod([AssetTrans].[ClosedDate full month name]) )
You can see the StrToMember function here:
https://help.pyramidanalytics.com/Content/Root/developer/reference/fx/PQL/Semantic/String/StrToMember.htm?Highlight=strtomember
I hope this clears things up,Imbar
-
@imbar thanks for your reply, I am trying this now but I keep getting a syntax error
-
Sophie durrant could you share the error?
did you make sure the set you are trying to create is under "ClosedDate"? it should be selected on the size panel when designing it:
also, what version of pyramid are you using? -
imbar so I tried the StrToMember by itself, and got the following error
-
imbar I have to say, the error messages are not the most helpful! We are on an old version 2018.05.425, but we're planning on an upgrade soon
-
Sophie durrant
ohh I see. 2 things are missing then:1. the caption function was added on version 2020
2. the "dot" function option was added on that same version.
The error is just saying that it's failing to understand what that . is (in the .caption) If you were to use the generic way of writing functions
caption([AssetTrans].[TransDate full month name].@[a0268f77-21f1-4005-918e-a73c97469204])
you would get an error of "caption function not supported".
I would suggest upgrading to keep up with the latest features.
For version 2018.05 I could suggest a different way to go:
Create a global text parameter with relevant options of months.
Then use the strToMember function in both the TransDate set and the DisposalDate set.
It should look something like this:
StrToMember([AssetTrans].[TransDate full month name], [global].#[92cdcf6f-25df-4334-b19b-56c3a3ca77e7])
By the way, another one of the newer features is a "free text" option in the global text parameter, so you won't have to set all the options on creation.
-
