MDX

Syndicate content 4 

I want to have a generic PercentOfAll measure that can be used for any dimension of the cube. This PercentOfAll should act like Count, but instead of showing the number of rows in a cell, it will show the percentage  (100*number of rows for that member/all rows) .It should not matter what dimension is being viewed. Ex

There are a total of 100 rows in the source class.

For dimension columnA which returns value in  ColumnA column in source class, 10 rows contain "A" the rest contain "null". When viewed on the analyzer, with columnA as a row and PercentOfAll as a measure, the cells values should be:

"A"       10%

"null"  90%

 I can achieve this using an expression that would be specific to columnA dimension Ex columnA. CurrentMember/columnA.[All columnA].%All

But I do not want to have to define meausure for each and every dimension. I just want to have a measure that will be used for all dimensions. Ex

Last answer 5 days ago
+ 1   0 1
0

comments

34

views

+ 1

rating

Hi, Community!

I’m sure you are using Developer Community analytics built with InterSystems Analytics technology DeepSee:

You can find DC analytics n InterSystems->Analytics menu.

DC Analytics shows interactive dashboards on key figures of DC entities: Posts, Comments, and Members. 

Since the last week, this analytics project is available for everyone with source code and data on DC Github!

Last comment 6 August 2018
+ 2   1 1
200

views

+ 2

rating

Hi, DeepSee experts!

I need to call a listing programmatically other than the default. 

I can do that with %LISTING keyword. E.g. in Samples:

SAMPLES>d $SYSTEM.DeepSee.Shell()
DeepSee Command Line Shell
----------------------------------------------------
Enter q to quit, ? for help.
>>DRILLTHROUGH SELECT FROM [HOLEFOODS] %LISTING [Listing]

   #  ID   Date City Chan Prod Unit Reve Disc Comm
   1: 475  01/1 Madr Reta Life 1    0.92 20%
   2: 843  01/1 Manc Onli Frui 1    4.95 0%
   3: 808  01/1 Osak Onli Bund 4    79.8 0%

But how can I call custom listings in MDX? E.g. there are 3 custom listings available in SAMPLES HoleFoods Cube:

Last answer 31 March 2018 Last comment 2 April 2018
0   0 3
169

views

0

rating

Hi, Community! 

This is the 3rd part of DeepSee Web story - Angular base UI for DeepSee Dashboards, see the beginning here.

By design, DSW provides an implementation for every widget in DeepSee library. But there are some extra features in DSW which make solutions built with DSW dashboards more functional.  This article describes it.

+ 2   1 1
0

comments

268

views

+ 2

rating

Hi, Community!

How can I manage to show in MDX query months where every month shows the value of the last month?

Say, in HoleFoods the query:

SELECT NON EMPTY [Measures].[Amount Sold] ON 0,NON EMPTY [DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HOLEFOODS]

will show me the sum of revenue per month.

This query:

SELECT NON EMPTY [Measures].[Amount Sold].MAX ON 0,NON EMPTY [DateOfSale].[Actual].[MonthSold].Members ON 1 FROM [HOLEFOODS]

will show the Max revenue in a day for the given month.

But what MDX will show the months with the revenue for the last day in a month?

 

Last answer 5 April 2017 Last comment 8 October 2017
0   0 0
724

views

0

rating

Based on user request in zenpage, i am running the method ConvertMDXtoJSON in background and loading the status in progressbar.

D ..%RunBackgroundMethod("ConvertMDXtoJSON",MDX)

From this background method ConvertMDXtoJSON() i want to return the value(sjson) and store it into session.

How can i do this?

Below is my code.

Last answer 7 April 2017 Last comment 10 April 2017
0   0 0
708

views

0

rating

Hi!

Sometimes I need to filter the widget on a dashboard from a different cube. And I face the following problem:

Widget A refers to a query from Cube A and I want to filter Widget B from Widget B.

Widget's B pivot refers to Cube B, and which has different dimensions for the same data.

E.g. cube A has the dimension Author and the Cube B has the dimension Member for the same data. So  there is no way to filter such a widget B from the widget A.

Actually, once we filter a given widget B with another widget A, we add the Filter Expression to the MDX query which looks like member's expression from Cube A, like:

[Outlet].[H1].[Region].&[Asia]

Is there any way to alter the filter expression for Widget B, just changing the value of the last part (Asia in this case) of the filter expression?

Last answer 9 April 2017
0   0 0
0

comments

140

views

0

rating

Hi,

suppose a part of my mdx query is [DIMENSION].[H1].[LEVEL1].CURRENTMEMBER.Property("PROP1").

If the source property of PROP1 is also used as a dimension level somehwere in the cube, this query will return a key  to the entry in the coresponding star table.

Does anybody know how I can follow that key and display the real value value?

 

 

Last answer 3 April 2017
0   0 3
0

comments

399

views

0

rating

How to setup dollar ranges to get the number of patient accounts. So, I want to capture the number of patient accounts based on the original balance of:
0 to 499
500 to 999
1000 to 2499
5000 to 9999
10000 to 24999
25000 to 49999
50000 to 74000
75000 to 99999

=100000

Last answer 30 March 2017
0   0 4
0

comments

139

views

0

rating

Hi, All!

What is the calculated measure expression for the percentage of a member?

Say in Holefoods I want to see in a Cols Online sales in Revenue, All the revenue and % of Online sales for the Revenue?

I have the following:

With expression:

SELECT NON EMPTY {NONEMPTYCROSSJOIN([Channel].[H1].[Channel Name].&[2],[Measures].[Amount Sold]),[Measures].[Amount Sold]} ON 0 FROM [HOLEFOODS]
How can I add the percentage of All in Revenue for Online channel?
Last answer 25 January 2017 Last comment 25 January 2017
0   0 1
214

views

0

rating

How are we doing THIS year versus the same period LAST year? 
This is a common need in Business Intelligence. In fact, many design specifications for reports make use of a comparison between a selected period (year, quarter, etc) up to a certain date (for example November 15th, 2016) and a summary of the same information for the previous year (i.e. up to November 15th, 2015). 
This post shows how to implement this in DeepSee.

Last comment 9 January 2017
+ 5   0 0
678

views

+ 5

rating

Hi-

I am trying to figure out how to compare two dimension values in a MDX query such that records where two dimension values are the same will be filtered out.

To test this, I have modified the HoleFoods cube in SAMPLES namespace to have a new dimension called RandomRegion

I want to create a MDX query where the [Outlet].[H1].[Region] value is not equal to the [RandomRegion].[H1].[RandomRegion] value.

Here's the query I need to add the filter to:

SELECT NON EMPTY HEAD(NONEMPTYCROSSJOIN([Outlet].[H1].[Region].Members,[RandomRegion].[H1].[RandomRegion].Members),2000,SAMPLE) ON 1 FROM [HOLEFOODS]

For example, if the Region = Asia and the RandomRegion = Asia I dont want to show that row in my result set.

Any thoughts on how I can accomplish this?

Last answer 7 December 2016 Last comment 7 December 2016
0   0 2
513

views

0

rating

Hello. I'm just getting started in DeepSee, and am also a newbie in MDX.

I am looking to create a pivot table that will be used to drive a bar chart. The chart will have product classes across the bottom, and two bars per class: a calculated measure to derive Net Sales for the specified period last year, and a straight aggregation of a standard measure for the same period for this year. Ideally, this period can be chosen by the user via filters on the dashboard, and it could be week, month, or quarter. So, obviously, it's the calculated field (and perhaps the entire MDX query) I need help with.

My current query, which is just returning the product classes and the net sales (CLineExt), looks like this:

SELECT [Measures].[CLineExt] ON 0,NON EMPTY [WebCat1Desc].[H1].[WebCat1Desc].Members ON 1 FROM [INVOICEDETAIL]

The above cube, InvoiceDetail, also has an InvDate dimension, as well as 4 other dimensions derived from it: InvYear, InvQuarter, InvMonth, InvWee

Last answer 6 December 2016 Last comment 9 November 2016
0   0 1
260

views

0

rating

Dear All,

From my web page, i am passing filter values to mdx query.

when mdx executes, i am getting Error #5001: Invalid member expression: empty name(2).

when i try the same in samples namespace (patient cube). I got the same error.

Below is my sample MDX from patients cube.

SELECT 
NON EMPTY %KPI("PluginDemo","HighScoreCount",,"%CONTEXT") 
ON 1 
FROM [PATIENTS] 
%FILTER %OR([HOMED].[H1].[ZIP].&[32000])

Result: 0   This works fine.

 

SELECT 
NON EMPTY %KPI("PluginDemo","HighScoreCount",,"%CONTEXT") 
ON 1 
FROM [PATIENTS] 
%FILTER %OR([HOMED].[H1].[ZIP].&[32006])

Result: 13   This works fine.


Now, when i try to access both 32000 and 32006. I got error

Last answer 30 November 2016 Last comment 1 December 2016
0   0 3
177

views

0

rating

I currently have an MDX query/calculated measure as per the below:

 
[DaysOnListRange].[H1].[DaysOnListRange].&[Days 91 - 180]+ [DaysOnListRange].[H1].[DaysOnListRange].&[Days 181 - 365]+
[DaysOnListRange].[H1].[DaysOnListRange].&[Days 365+]

however it currently doesn't sum when there is a blank in the data.
How can I amend this query to include empty cells/only sum cells with data?

I've tried using nonempty but it doesn't appear to work in this context

Last answer 21 November 2016 Last comment 22 November 2016
0   0 3
204

views

0

rating

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?

Last comment 14 October 2016
0   0 4
0

answers

174

views

0

rating

In DeepSee, having a customer account cube. The cube includes a time dimension AccountCreationDate.

How do you create a calculated measure to get the total number of accounts from the beginning to a certain date in abscissa ?

I tried many things including this:
AGGREGATE(%TIMERANGE([AccountCreationDate].[H1].[MonthYear].&[190001],[AccountCreationDate].[H1].[MonthYear].CURRENTMEMBER) ,[Measures].[%Count])

But I don't get the right result because the date in abscissa creates a filter on the first date.

I tried also with %MDX function, but then I loose the context for the CURRENTMEMBER.

Last answer 8 June 2016 Last comment 8 June 2016
+ 1   0 1
448

views

+ 1

rating

Hi all,

I have a dashboard which shows the growth of the months compared with the month from the last year.

The way I found to develop that, it was with a kpi. So, I overrode the %OnLoadKPI method with two mdx:

SELECT NON EMPTY [DataD].[H1].[MothYear].Members ON ROWS, [Measures].[CountVisits] ON COLUMNS FROM SubjectAtendimento %FILTER [DataD].[H1].[Year].&[2016]

SELECT NON EMPTY [DataD].[H1].[MonthYear].Members ON ROWS, [Measures].[CountVisits] ON COLUMNS FROM SubjectAtendimento %FILTER [DataD].[H1].[Year].&[2015]

And then, I set the %..data variable with the percent growth of the months for each year.
With kpi is very easy to do that, but, I wouldnt like to do that KPI. Instead, I would like to have a pivot, so t I need to have just one MDX.

Do you have any suggestion?

Last answer 13 May 2016 Last comment 16 May 2016
0   0 1
209

views

0

rating

The DeepSee Shell Best Practices Series - Example of using the MDX subquery flag in the DeepSee Shell

We have different flags for DeepSee advisors to look into the execution details and the methods DeepSee is using to do calculations.

flag [flagname] [on|off] – Turn a diagnostic on or off.

flag cmbr on|off – Show current member processing.

flag compound on|off – Show rewrite of compound queries.

flag crossjoin on|off – Show crossjoin processing.

flag relations on|off – Show relationship processing.

flag rewrite on|off – Show rewrite of query.

flag subquery on|off – Show subquery processing.

CLEAR – Kill flag value

+ 2   0 1
0

comments

280

views

+ 2

rating

The DeepSee Shell best Practices Series - Execute an MDX query in the DeepSee Shell with/without  results cache

This cache is different from cache reset. Cache reset clears everything in the namespace but “cache off” only clears the cache in ^DeepSee.Cache.Results and ^DeepSee.Cache.Axis global node for the corresponding cube. The difference is quite smaller in the case as below, but in some cases it can be a big difference.

^DeepSee.Cache.Results, which contains values for each query previously executed for a given cube. This global also contains meta-information about those queries that can be used to quickly rerun them.

^DeepSee.Cache.Axis, which contains metadata about the axes of previously run queries. DeepSee uses this information whenever it needs to iterate through the axes of a given query

+ 2   0 1
0

comments

173

views

+ 2

rating