Two measures cannot be cross joined

Question

Question

Razvan Prepelita · Aug 30, 2017

#Caché #InterSystems IRIS BI (DeepSee) #Cubes #Dashboards #Tutorial

Hello, I've completed this tutorial: https://community.intersystems.com/post/deepsee-period-date-vs-same-peri...
I've used the "year" level instead of a pivot variable.
I want to calculate something like the following variation: 100 * ([DateOfSale].[PTD] - [DateOfSale].[LYPTD]) / [DateOfSale].[LYPTD]
I create a new calculated member, under the same dimmension with the above expression.
I put the new calculated member under the Count measure and it tells me that "Two measures cannot be crossjoined"
What am I doing wrong?
Can you redirect me to some explanations and maybe a solution?
Thanks!

Discussion (3)0

Log in or sign up to continue

Alessandro Marin · Aug 30, 2017

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.

1 0

score 0 · Answer 1 · 2017-08-31T06:55:52-04:00

Hello Alessandro,
It helped, thank you.
I now have LYPD, PTD and Percentage on Columns and CustomerName on Rows.
The problem appears when I try to put a measure in the Measures field. I've tried even with Count and I receive the following error:
"ERROR #5002: Cache Error: <UNDEFINED>%GetAxisSpecs+102^%DeepSee.Component.pivotTable.1 *pItems(4,4) (8) "

score 1 · Answer 2 · 2017-08-31T15:08:46-04:00

I can reproduce the error and I will look into it more carefully. In the mean time I think you should be able to achieve the same result by doing exactly what I show in my screenshot above: put Count and your calculated member on Columns, then crossjoin PTD and LYPTD with Count.