New post
Question
Rich Taylor · Dec 14, 2015

Creating Trend line (scorecard) from data rather than counts

#MDX #InterSystems IRIS BI (DeepSee)

I need to create a KPI similar to the  DeepSee.Model.KPIs.DemoTrendLines kpi in Samples.  This kpi supports a scorecard widget with a trend line.  It shows patient counts by city with a trend line of the count of patients over a decade.  What I want is to show the trend line based on the average allergy score ([Measures].[Avg Test Score]).  A pivot table query that shows the data I would want to base the trend line on is:

SELECT NON EMPTY [BirthD].[H1].[Decade].Members ON 0,
NON EMPTY [HomeD].[H1].[City].Members ON 1 
FROM [Patients] %FILTER [Measures].[Avg Test Score]

Has anyone done this?

0
0 277
Discussion (3)2
Log in or sign up to continue
Rich Taylor · Dec 14, 2015

Found the solution.  The following MDX gives the values that I want.

SELECT {MEASURES.[Avg Test Score],
%LIST(NONEMPTYCROSSJOIN([BirthD].[H1].[Decade].Members,{[Measures].[Avg Test Score]}))} ON 0,
NON EMPTY homed.city.MEMBERS ON 1
FROM patients

I think the key was to enclose the measure in the NONEMPTYCROSSJOIN function in curly braces.  I had not done this in a previous attempt at getting this to work.

0 0
Lexi Hayden · Dec 16, 2015

It does not make sense that you would have to use curly braces here. I get the same results for your query with or without them.

But anyway, am glad you got the MDX you wanted.

0 0
Peter Steiwer · Oct 14, 2016

This documentation page describes the use of { } within MDX. As Lexi points out, a single member inside of { } should behave the same way without the { }. This is the syntax for a set. If you come across something that is not working as expected without the { }, please consider submitting a WRC so we can get the behavior resolved.

1 0