Cumulative context but to exclude specific status measures (unable to reproduce solution)
![](https://s3-us-west-2.amazonaws.com/media.forumbee.com/i/9d41c213-a23e-4605-af71-12e07cd36c2c/h/547.jpg)
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