nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Use dimensions in measure expression

trespass

Hi all, I need to use dimension object into a measure expression. Once built the cube with, for example, YEAR dimension and AMOUNT measure, how can I add a new measure in a crosstab like: AMOUNT_NEW = AMOUNT / "NUMBER OF ROWS EXTRACTED FROM YEAR DIMENSIONS" ? Is it possible? I can't find a way... I thought there was something like: dimension[TIME][YEAR].count thanks in advance, Massimo

Find more posts tagged with

Comments

Hans_vd

I believe you can do it like this:- create a measure that uses function COUNT- in the crosstab right click on a measure and choose Add Derived Measure- you can now build an expression that will look - depending on how you named your dimensions and measures - something like this:<pre class="_prettyXprint">data["AMOUNT_dimYear/YEAR"] / data["MYCOUNT_dimYear/YEAR"]</pre>

trespass

Hi, thanks so much for your answer, I've tried to create a measure with function COUNT referring to a DIMENSION, but it didn't work.How can I create a cube measure referring to a dimension?I've attached what I'm trying to do. regards, Massimo

Hans_vd

Why should it be "referring to a dimension"?Is the dimension by which the amount is reported different from the dimension of the count you want it to be divided by?

trespass

No no, what I mean is how can I build the [color=rgb(0,136,0);font-size:14px;]"MYCOUNT_dimYear/YEAR" [/color]in your example?

thanks

Hans_vd

When you drag the measure to the crosstab, it will be automatically created. (You can hide it if you want by selecting "Show/Hide Measure" from the context menu)Next step is then using that measure item in the the derived measure

trespass

But I'm not able to create a measure into the cube using "[color=rgb(0,136,0);font-family:helvetica, arial, sans-serif;font-size:14px;]dimYear/YEAR" [/color][font="helvetica, arial, sans-serif;font-size:14px;"]because it is a group (dimension)!

[/font][font="helvetica, arial, sans-serif;font-size:14px;"] [/font] Is there an alternative such as count extracted rows of a crosstab column? many thanks

Hans_vd

I'm not saying you need to create a measure in the cube using a dimension.Create a measure using function COUNT, it doesn't matter what field you put in the expression. Then, when you drag the measure in the crosstab, the dimYear/YEAR like stuff will be created. You can then - again in the crosstab, not the cube - create a Derived Measure where you can put an expression together using the crosstab measures that you already have in the crosstab.

trespass

thanks a lot! It works!a last question: how can I use a crosstab column called "XX" (not row) in a javascript?data["XX"] and row["XX"] refer to a single row, but if I want to put a crosstab column into an array, how could it be done? example: new Array(column["XX"]) thanks

Hans_vd

Hi trespass,That's great! Don't know about the scripting.Maybe it's better to start a new thread in this forum. Or maybe if you describe what it is you want to achieve, we can work out a solution that doesn't need this kind of scripting.

trespass

thanks again! I'll open a new thread about it. cheers