DeepSee

Syndicate content 49 

I am trying to create a query that returns the best and worst performing products for a given customer, based on this year's net sales versus last year's net sales, weighted by the total net sales for all of the products sold to this customer in the last two years.

I have created Last Year Net Sales (up to the last month end): AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])

This year Net Sales: AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales])

Percent change: ((AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-1]),measures.[Net Sales]) - AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) /AGGREGATE(PERIODSTODATE([Invoice Date].[H1].[YEAR],[Invoice Date].[H1].[Month].[NOW-13]),measures.[Net Sales])) * 100

Last answer 30 December 2016 Last comment 29 December 2016
0 4
152

views

0

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 2
482

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 1
240

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 3
163

views

0

rating

When creating a filter located on a dashboard that targets all, is there a way to narrow that filter down?

For example, in our data we have some dates that are well out of range due to data quality issues, but they still appear in the drop downs. Is there a way to say, only make available the below dates in the filter?

Another example, If we want to only look at Emergency data, it still provides all the wards for inpatient activity and activity in other hospitals, meaning, to get to just the Emergency wards you have to scroll through everything?

Hope that makes sense.

Also, to follow this up, is there a way to order the drop downs? as for dates the oldest dates generally appear to be at the top where as we would prefer it to start with the most recent dates.

Last answer 23 November 2016 Last comment 23 November 2016
0 4
165

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 3
195

views

0

rating

In DeepSee we have a field that is numeric but is used as a dimension. In DeepSee it sorts this field in the following order. 10, 11, 15, 2, 3, 5, 6, 8, 990. Is there a way to have DeepSee sort it numerically instead of treating it like a character field? We would want to see it show up in the dimension as 2,3,5,6,7,10,11,15,990.

 

Last answer 17 November 2016
0 1
0

comments

128

views

0

rating

Hi-

I was wondering if there was a DeepSee overview document available for a non-technical user.  Something short and concise describing what DeepSee is and its key features.

I've looked at the documentation and although there are parts of what I am looking for there it is much too technical for my needs.

Thanks

Last answer 27 October 2016
0 1
0

comments

133

views

0

rating

I know per documentation (pasted below) to set the DSTIME parameters for the base class of a cube.  If that base class has a property that is another class, do I have to add the DSTIME parameters to that secondary class? 

Documentation says the following in a section titled "Enabling Cube Synchronization:"

Before you can synchronize a cube, you must enable the cube synchronization feature for that cube. To do so:

1.   Make sure that cube synchronization is possible in your scenario. See “When Cube Synchronization Is Possible,” earlier in this chapter.

2.   Add the DSTIME parameter to the base class used by that cube...

Last answer 15 September 2016 Last comment 23 September 2016
0 2
240

views

0

rating

I installed MDX2JSON and DeepSeeWeb 1.4.46b on this version of Caché (actually HealthShare) 

Cache for Windows (x86-64) 2016.1 (Build 656U) Fri Mar 11 2016 17:42:42 EST

I works well for the most part but my buttons contain characters that should not be there. Here is a screen shot. Does anyone recognize what the issue might be?

 

Last answer 18 July 2016 Last comment 19 July 2016
0 1
167

views

+ 1

rating

I  have a DeepSee KPI defined based on %DeepSee.KPI following the documentation http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=....  I've then enabled the KPI on a dashboard by adding a Widget where the data source is the KPI.  Currently the ability to show a Detail Listing is implemented by the call back %OnGetListingSQL.  This method documents the parameter

pListingName is the name of the listing to display. This is reserved for future use.

 

According to this and from what I can tell there is no way to provide support for a secondary listing. 

Last answer 14 July 2016 Last comment 14 July 2016
0 1
144

views

+ 1

rating

I am trying to calculate a dimension percentage for WFRole on a child cube named MbMRouteHistoryInitiatorObj joined to a parent cube named MbmQaSObj by using a calculated member dimension within MbmQaSObj. 

If I use the calculated member dimension value expression of [WFRole].[H1].[WFRole].CurrentMember/ [WFRole].[All WFRole].%ALL in the child cube MbMRouteHistoryInitiatorObj, the percentages work correctly.

I am not able to calculate the same percentage using the value expression [MbmRouteHistoryInitiatorObj].[WFRole].[H1].[WFRole].CurrentMember/ [MbmRouteHistoryInitiatorObj].[WFRole].[All WFRole].%ALLin the parent cube MbmQaSObj.  The %ALL gives an error.  Any ideas on how to make this work?

 

Last comment 22 June 2016
0 2
0

answers

261

views

0

rating

I need to pass multiple values to a FILTER to a DeepSee DASHBOARD via the URL. The documentation seems to indicate this can be done, i.e.: "Where each filterspec is filter_name.filter_values" but there are no examples I can find that indicates what is used to separate multiple values. Say I want to pass the three values 5, 6, and 1395 to a filter. To pass just the 5 I know this works:

&SETTINGS=FILTER:LEAD.MYUSERID.H1.MYUSERID.5

but how do I include 6 and 1395. None of the following seem to work:

&SETTINGS=FILTER:LEAD.MYUSERID.H1.MYUSERID.5.6.1395

&SETTINGS=FILTER:LEAD.MYUSERID.H1.MYUSERID.5,6,1395

What is the proper syntax?

 

Thanks.

Last answer 13 June 2016 Last comment 13 June 2016
0 1
187

views

+ 1

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
0 1
415

views

+ 1

rating