New post
Question
· Mar 19, 2018

Generic date filter that affects different widjets with different dimension dates

#Dashboards #InterSystems IRIS BI (DeepSee)

Hi Evgency,

Effectively I have widjet with pivot which include dates in from different cubes/dimensions, but exists the relation between the two cubes.

I'm showing below the MDX query from one pivot that uses the date "Fecha Creacion" that comes from cube "EVENTOSPOLICIALES" 

SELECT %LABEL([Measures].[%COUNT],"Cantidad de Encargos","") ON 0,
       NON EMPTY {[TipoEventoPolicialD].[H1].[TipoEventoPolicial].&[Encargo de Arma],
                [TipoEventoPolicialD].[H1].[TipoEventoPolicial].&[Encargo de Especies],
                [TipoEventoPolicialD].[H1].[TipoEventoPolicial].&[Encargo de Personas],
                [TipoEventoPolicialD].[H1].[TipoEventoPolicial].&[Encargo de Vehículos]} ON 1
FROM [EVENTOSPOLICIALES]

Here, I'm showing the other MDX query that uses the date "Fecha Parte" like a filter that comes from the Cube "PARTEPOLICIAL", bit the others fields come from "EVENTOSPOLICIALES"

SELECT NON EMPTY [Detenidos].[Edad].[H1].[RangoEdad].Members ON 0,
       NON EMPTY [Detenidos].[Sexo].[H1].[Descripcion].Members ON 1
FROM [EVENTOSPOLICIALES]

 The idea is to have a generic date filter that when I choose a range of date affects both "Fecha Creacion" and "Fecha Parte".

The client wants this functionality, but if it's no possible, doesn´t matter, I can tell them that they have to choose two different dates.  

Thanks for advance. 

Discussion (2)0
Log in or sign up to continue
Alessandro Marin · Mar 19, 2018

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.

0 0