TM1 Forum

I would like some ideas/suggestion about solving the following issue:
I have a cube with the following dimensions: A,B,C,D, M (Measure)
The Measure M has fields called: Amount, Dispute Flag, Dispute Status

I would like to create a Cube view that show only data when M. Dispute Flag > 0

Any help will be appreciated.
Thanks

Hello,

What is your view layout / how do you select elements ?
Do you have several elements in the rows / columns ?
Or for example, 1 dimension in the rows and the other dimensions in the titles ?

This is the type of filter I am using:

TM1SORT(FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Product]) , 0) , [Product Cube].([Versions].[Actual],[Period Daily].CURRENTMEMBER,[Prod Types].CURRENTMEMBER,[Invoice Dates].CURRENTMEMBER,[Product Category].CURRENTMEMBER,[Product Cube Measures].[Open Invoice Flag]) = 1) , ASC)

When this MDX statement is applied to the cube view It still show products that have [Open Invoice Flag] 1 and 0.

Cube has 5 Dimensions on Rows and 3 on Column, Product is on a row

Thanks

Which dimensions are used in context (title dimensions) and which are used in rows/columns?

There are a couple of approaches. The easiest, by far, is with one row dimension and one column dimension, one of which being the measure dimension. That MDX is pretty straightfoward and does not require any reference to the title dimensions as those are automatically taken into account as context. If your business need can be fitted within this framework, this is by far the fastest performing option.

Assuming the Measure dimension is the column, it gets a wee bit more difficult if two dimensions are used on the row. In this case, you'd need two MDX statements, one for each dimension which joins the other row dimension, using GENERATE and FILTER. Depending on the size of the dimensions, this could be a bit slow.
In this case, if you want three or four dimensions on the rows, then an MDX view is going to be the ticket, although you could continue to wrap GENERATE around the queries described above, and it, too, may be a bit slow at times. I've pretty much quit using MDX views in TM1 except in special circumstances (like this!). I'll get an example in a bit if someone else does not provide one.

MDX example View as promised:

Thanks a lot "PavoGA" I was able to follow your suggestions and I created a view based on you MDX query

SELECT
[M].members ON COLUMNS,
FILTER( [A].members *
.members *
[C].members *
[D].members,
[M].[Dispute Flag] > 0)
ON ROWS
FROM [cubename]

The only odd think is that I was not able to create such view using ViewCreateByMDX in a TI process.

Thanks

mlorini wrote: ↑Tue Aug 03, 2021 9:09 pm

The only odd think is that I was not able to create such view using ViewCreateByMDX in a TI process.

Thanks

This should be a fairly straightforward MDX view, but there are some differences in how the various user interfaces handle MDX in TM1. I do not have one readily available, but you might search the forum for some of the discussions on it.