There is no direct equivalent in ObjectScript to the ternary operator. You can use $SELECT or $CASE

• Those listings in italics are defined in Listing Groups. You can find them in SMP > DeepSee > Tools > Listing Group Manager > Open > Additional Listing for HoleFoods Sample
  They work in MDX:
  Do $SYSTEM.DeepSee.Shell()
  >>DRILLTHROUGH SELECT FROM [holefoods] %LISTING [Another Sample Listing by Date]
   

• "Custom Listing" is the listing that you construct in pivots using Listing Fields, see here: Display a Detail Listing. I have not checked the code but to me it makes sense that, if defined on the pivot, "Custom Listing" will not be available for general MDX queries.

In spite of the fact that your queries do not show dates nor the PARTEPOLICIAL cube, I do not think related cubes change anything from what suggested in answer 2 here: https://community.intersystems.com/post/filters-deepsee-dashboards . Below I rephrase what was suggested:

A) Create a "myDate" pivot variable used as "Literal" and of type "Day" in your main cube, which I will assume is EVENTOSPOLICIALES.

B) Define two calculated dimensions. First get the spec of the two dates from your main cube. You can drag and drop dates and inspect the MDX query. For example, the specs will be something like:

[Some dimension].[H1].[Fecha Creacion]

[Relation to PARTEPOLICIAL].[Some other dimension].[H1].[Fecha Parte]

C) Create two calculated dimensions with Local Storage. The expressions should use the specs in B and the pivot variable in A, for example:

[Some dimension].[H1].[Fecha Creacion].$variable.myDate

[Relation to PARTEPOLICIAL].[Some other dimension].[H1].[Fecha Parte].$variable.myDate

D) Use the calculated dimensions as filter or in rows/columns for your pivots. The dashboard will have to have a widget with a Apply Pivot Variable control.

That happened because calculated members and pivot variables are stored in globals. One way to export folder items together with related supporting items is to follow the procedure described here:

http://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=D2IMP_ch_moving#D2IMP_folder_manager_export_container

You can find the link to Management Portal in the documentation:  http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSA_using_portal 

To see if the instance is running: I suggest getting familiar with the ccontrol command from terminal: http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSA_using_instance#GSA_using_instance_control

For example, ccontrol list will list basic information about your instance. This is an example on my (Linux) computer where you can see that the first instance is down and the second is running: 

amarin> ccontrol list

Configuration 'C152'
    directory:    /home/amarin/intersystems/cache20152
    versionid:    2015.2.1.705.0
    datadir:      /home/amarin/intersystems/cache20152
    conf file:    cache.cpf  (SuperServer port = 56778, WebServer = 57778)
    status:       down, last used Fri Jul  7 09:37:47 2017

Configuration 'C161'
    directory:    /home/amarin/intersystems/cache20161
    versionid:    2016.1.0.610.0
    datadir:      /home/amarin/intersystems/cache20161
    conf file:    cache.cpf  (SuperServer port = 56777, WebServer = 57777)
    status:       running, since Wed Nov  8 11:33:16 2017
    state:        ok
 

No problem, your case (no time part in the timestamp, just get the fact that comes last in your source table) is even easier. Once you make the fact order in your source table work this plugin should work.

This plugin uses SensorReading, but it is possible to make it more dynamic (hint: implement a filter definition and %OnGetListingFields). 

/// Use this plugin in a calculated measure as %KPI("User.LastFact","LASTFACT",1,"%CONTEXT")
Class User.PluginLast Extends %DeepSee.KPIPlugIn
{

/// Cube(s) that this plug-in is based on.
Parameter BASECUBE = "*";

/// SourceTable
Parameter LISTINGSOURCE = "SourceTable";

Parameter PLUGINTYPE = "Pivot";

// The ID is probably not even needed:
Parameter LISTINGFIELDS = "SensorReading, %ID as ID";

XData KPI [ XMLNamespace = "http://www.intersystems.com/deepsee/kpi" ]
{
<kpi name="User.LastFact" displayName="LastFact" caption="LastFact" >

<filter name="%cube" displayName="Subject Area"/>
<property name="LASTFACT" displayName="LastFact" description="" />

</kpi>
}

/// Get the base query for this plug-in.
Method %OnGetMDX(ByRef pMDX As %String) As %Status
{
    Set tBaseCube = ""

    // Use %cube filter to find the base cube
    If $IsObject(..%filterValues) {
        If (..%filterValues.%cube'="") {
            Set tBaseCube = ..%filterValues.%cube
        }
    }

    If (tBaseCube'="") {
        Set pMDX = "SELECT FROM "_tBaseCube
    }
    Quit $$$OK
}

/// Compute the LastFact of values within the result set.
Method %OnCompute(pSQLRS As %SQL.StatementResult, pFactCount As %Integer) As %Status
{
    Set tSC = $$$OK
    Try {
        Set tErrMsg = ""
        While (pSQLRS.%Next(.tSC)) { 
            If $$$ISERR(tSC) {
                Set tErrMsg = $System.Status.GetErrorText(tSC)
                Quit
            }
            // Get the last fact added to the table. If unsure use pSQLRS.ID
            Set tValue = pSQLRS.SensorReading
            
            If ($ZSTRIP($P(tValue,":"),"<>W")="ERROR") {
                Set tErrMsg = $P(tValue,":",2)
                Quit
            }
        }

        // place answer in KPI output
        Set ..%seriesCount = 1
        Set ..%seriesNames(1) = "LastFact"
        If (tErrMsg'="") {
            Set ..%data(1,"LASTFACT") = tErrMsg
        } Else {
            Set ..%data(1,"LASTFACT") = tValue
        }
    }
    Catch(ex) {
        Set tSC = ex.AsStatus()
    }
    Quit tSC
}

}
 

In Steve's example there are multiple (two) facts in one day, and he need to get the latest. IMO this means that Steve need to get the time of the reading (or maybe get the reading in a certain day that was added to the source table last). When using time dimensions we do not have a level more granular than DayMonthYear that can compare the time part of a reading.

I tried working around this problem but so far without success. With a plugin we have access to the lowest-level data, in particular the whole time stamp containing the time of the reading (I am assuming Steve used in his source class a regular time stamp). In my test I was able to use that piece of information to get all readings in one single period (day, month,..), compare the time stamps, and return only the most recent.

Unless Steve is ok with using Average as the aggregation function for his SensorReading measure as suggested by Asaf

Hello Razvan,

I assume you are starting from the last figure in the tutorial where the pivot in the figure has the Count measure on Columns.

Your calculated member is considered a measure because it performs a calculation, not a "pure" aggregation of facts. Two measures cannot be crossjoined (that is you cannot place one under the other, or one in the Columns and the other in the Measures box),and for this reason you see an error. In this page you can find more details on why you cannot crossjoin two measures:

https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GCNV_R2015_2

That said, try creating a "Percentage" calculated measure with this expression: 

100 * (%CELLZERO(-1,0) - %CELLZERO(-2,0))/%CELLZERO(-1,0)

and place it in the Columns box (do not crossjoin it with Count though!). 

I hope this helps.

One way to do this is by using a pivot variable. Create the same pivot variable "Region" in both pivots on which your widgets are based. These pivot variables should return the members, in your example Asia, Europe, N. America, S. America. You can define the manually or in a termlist, or use a kpi to retrieve them. 

For the example in the screenshot below I created a HoleFood2 cube with a Outlet2.H1.Region2 level. This level is "incompatible" with an Outlet.H1.Region level in HoleFoods. In my manual Region pivot variable I simply defined two regions, which can be selected manually. 

Once you have these two pivot variables create a calculated dimension on each pivot using the pivot variable. In your example in HoleFoods the expression should be Outlet.[$variable.Region]. Place the calculated dimension on Filters.

This is how I did it in HoleFoods:

and this is how I did it in HoleFoods2:

Finally, add an ApplyVariable control on one of your widgets with "*" as target. Selecting a region will filter both widgets.

%LAST evaluates a measure or numeric expression for the last non-empty member of a set, so I do not think that is what you want.

I am using %LASTCHILD, but you cannot append it to [DateOfSale].[Actual].[MonthSold].Members because %LASTCHILD only works on specific member. For the moment I found this solution:

WITH  MEMBER [DateOfSale].[LastDayOfMonth] AS '[DateOfSale].[Actual].[MonthSold].CurrentMember.LASTCHILD' 

SELECT [Measures].[Amount Sold] ON 0,

NON EMPTY NONEMPTYCROSSJOIN([DateOfSale].[Actual].[MonthSold].Members,[DATEOFSALE].[LASTDAYOFMONTH]) ON 1 FROM [HOLEFOODS]

The resulting headers are ugly and I have not yet been able to successfully use [DateOfSale].[Actual].[MonthSold].CurrentMember.Properties("Name")

I do not think there is a way to ask the user to select a user before displaying the dashboard. 

Maybe you can try setting the filter as required in the dashboard settings and as default value using a run-time variable. The run-time variable should return the current username. To return the current user start from the COS $username special variable. 

You can use an applyFIlter or setFilter control and under Filter select "Named Filters". In the resulting control you will be able to select the named filters you defined on your pivot.

In the options for pivot table you can use Count instead of Grand Total as Summary. However, you will have to choose between the Grand Total and Count. If you calculate the grand total in some other way (for example by placing another [Outlet].[H1].[Region].Members and then Gear icon > Compute Aggregate > SUM), that COUNT will also count the cell with the Grand Total. 

Otherwise you can create a "Row Number" calculated measure with the expression below and place it on measures next to Revenue

COUNT(%CELL(-1,0))-1+%CELL(0,-1)

Hello Samuel,

I posted an article on Developer Community with a possible solution for this implementation. Please check this article. 

Thanks

Hello Benjamin,

I suggest we discuss these issues in a WRC or TRC. This is the link to Support.

For example, we might want to look into some of these concept: Time vs Data Dimensions, sorting the members in a level from Architect, and the "Depends on" setting for levels that can be set in Architect.

Thanks

Hello Samuel,

This is definitely not a newbie question, and ParallelPeriod could be very useful for the MDX you need. However, your pivot table has to be dynamic enough to work with different periods (Quarter, Month, Week). For this reason we might have to work on a KPI. I have two questions:

1) If I understand you correctly you want to be able to select any period (such as Jan-2014 or Week 2 of 2015), not only the current one (such as Nov-2016 or Q4-2016). Is that correct?

2) If so, what filters you like to appear on your dashboard? For example, would you like to have a dropdown menu to select Quarter/Mont/Week and a "dynamic" filter showing a Quarter/Month/Week for all years in your data?
 

Hello Alexandre,

Yes, DeepSee supports hierarchical structures. 

DeepSee models are based on cubes, which define elements such as Measures, Dimensions, Listings, etc. The hierarchical structure in your question can be modeled using a Dimension containing one hierarchy with three levels. The levels in the hierarchy are, in order, the Group, Name, and Code levels. Once you define this model in Architect you will be able to use the hierarchical structure in DeepSee Analyzer.

This is the link to a video in the Video Portal. The video I link explains how to define Dimensions in DeepSee cubes: http://video.intersystems.com/video/Video.Pages.VideoLibrary.cls?video=2663141302001&playlistid=2621105743001
 

This is the link to the "Defining DeepSee Models" book in the documentation: http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=D2MODEL

I recommend reading section 2.2 "Dimensions, Hierarchies, and Levels". There you will find an example based on the Patients cube where an Age dimension (AgeD) includes one hierarchy with three levels ordered from the least to the most granular:

I also recommend reading section 4.6 "Defining Hierarchies Appropriately".