Cumulative context but to exclude specific status measures (unable to reproduce solution)
Hello,
Background: We use Pyramid 2020.13.100 against a SSAS datasource.
I'm trying to make the graph above, my assumption is that I should be able to do that if I'm able to make the accompanying table.
I've tried this and this solution on the forum but I run into a MDX-error or it doesn't behave the way I expect it to. When I try to use the IF in stead of the IIF I also get an error.
Option1 = Visual formula (See accompanyning screenshot) gives MDX-error:
(Translation: Afgesloten -> Closed, Werkelijk bedrag -> Actual Amount)
MDX query failed because of:
Query (4, 70) The syntax for 'CurrentMember' is incorrect. (WITH
/* --- Pyramid 2020.13.100 CUSTOM_MEASURE by bonfreree@philadelphia.nl --- */
MEMBER [Measures].[+d32493a0-93c6-4a34-a862-951eabf97bcb+] AS IIf(CurrentMember([Financiële Periode].[StatusPeriode])=[Financiële Periode].[StatusPeriode].&[Closed],[Measures].[Actual Amount],null), CAPTION='d32493a0-93c6-4a34-a862-951eabf97bcb'
SET [measure_set] as DISTINCT({[Measures].[+d32493a0-93c6-4a34-a862-951eabf97bcb+]})
SELECT
{[measure_set]}
ON COLUMNS
FROM [Financieel Resultaat]
CELL PROPERTIES VALUE).
Query: WITH
/* --- Pyramid 2020.13.100 CUSTOM_MEASURE by bonfreree@philadelphia.nl --- */
MEMBER [Measures].[+d32493a0-93c6-4a34-a862-951eabf97bcb+] AS IIf(CurrentMember([Financiële Periode].[StatusPeriode])=[Financiële Periode].[StatusPeriode].&[Closed],[Measures].[Actual Amount],null), CAPTION='d32493a0-93c6-4a34-a862-951eabf97bcb'
SET [measure_set] as DISTINCT({[Measures].[+d32493a0-93c6-4a34-a862-951eabf97bcb+]})
SELECT
{[measure_set]}
ON COLUMNS
FROM [Financieel Resultaat]
CELL PROPERTIES VALUE
Option 2 = Script:
IIf([Financiële Periode].[StatusPeriode].CurrentMember([StatusPeriode])=[Financiële Periode].[StatusPeriode].&[Closed], [Measures].[Actual Amount],null)
Shows amounts for StatusPeriode=Closed as well.
9 replies
-
The problem you are running into is a mix of PQL and MDX. When using an SSAS datasource, all calculations must use MDX only. For example, in your screenshot of your calculation, you are trying to use the CurrentMember function. However, the version of the function you are using is PQL and not MDX. The MDX syntax would look like this:
IIF(<Model Attribute>.CurrentMember IS <Member>, <Measure>, NULL)
You will notice in the example above that I am using the IS operator instead of =. That is because the CurrentMember function in MDX returns a member object and not a string so to compare it with a member you must use IS or you can add the NAME function to get the string value (<Model Attribute>.CurrentMember.Name = "Married").Once you create the calculation, you can then use the Cumulative Total content calculation to create the running total you want.
-
It is also possible to write your own cumulative total in MDX as well if you need it. The easiest way to do running totals against dates is to use the YTD function in conjunction with an AGGREGATE function like this:
If the dates span multiple years, you could use the Range List object instead of the Year to Date object. Hope this helps!
-Jason
-
Jason Picker This does not work for me, I tried both ways:
Using YTD, I get the error-message that "By default, a year level was expected. No such level was found in the cube." This is something that I can only fix in our SSAS.
Using the Range List I get "Range operator ( : ) operands' have different levels; they must be the same."
-
Erik Date functions like MTD and YTD are dependent on the developer of the cube properly setting the column type property to a Date type. If the Financial Period attribute is actually a hierarchy, then you can use the PeriodsToDate function instead of the YTD function even if the attribute has not been configured as a Date type. If Financial Period is just a flat list, then the Range is your best option. The Range function does require both sides of the operation to come from the same attribute and the same level. For example, you can't have the left side set to a Month and the right side using a Year. Feel free to include a screenshot of the logic that you created using the Range function so I can help troubleshoot it further.
-
Jason Picker These are the start and endpoint of the Range from which I get the error message.
(The Context has Measures as Selected Dimension and Selected Hierarchy)
If you need more information, I'm happy to send it.
Full Error message=
MDX query failed because of:
Range operator ( : ) operands' have different levels; they must be the same.Query: WITH
/* --- Pyramid 2020.15.046 CUSTOM_MEASURE by bonfreree@philadelphia.nl on MS_OLAP_TABULAR 13.0.5622.0 --- */MEMBER [Measures].[+ffc2a9a2-8e66-4c4d-9964-f79295fae7ca+] AS AGGREGATE(([Financiële Periode].[Financiële Periode Jaar].&[jan 2021]:[Financiële Periode].[Financiële Periode Jaar].CurrentMember),[Measures].[Werkelijk bedrag]), CAPTION='ffc2a9a2-8e66-4c4d-9964-f79295fae7ca'
SET [measure_set] as DISTINCT({[Measures].[+ffc2a9a2-8e66-4c4d-9964-f79295fae7ca+]})SELECT
{[measure_set]}
ON COLUMNSFROM [Financieel Resultaat]
CELL PROPERTIES VALUE -
Jason Picker : Are you able to find the reason why I get the error-message?
-
Erik I realized that I was working on a MSOLAP Cube and just saw in your screenshot that you are using SSAS Tabular. I switched to that engine and was able to reproduce the error message you were seeing. I will talk to our Support team about it. However, I discovered a couple interesting things. First of all, it appears that Tabular models prefer the SUM function instead of the AGGREGATE function. You will see in my screenshot below that when I test the measure, I get the same message you were seeing.
I know the MDX is in fact valid so I decided to go ahead and save my formula and try it in a report. It turns out that the formula worked great!
It may be that the way the SSAS engine is interpreting the test query is the problem and not the actual MDX formula. Go ahead and see if you are getting an error in the report or just when testing the formula.
-
Jason Picker This has worked for me, I am now able to reproduce the graph, thank you!
-
