Custom Column - Creating calculated column using a Property and a Measure?

Hi. I have a question regarding Custom Mode and building calculated columns. My current environment has a property of Sales Pricing Unit Size and a measure of Qty. Is there a way that I can create a calculated column so that Qty / Sales Pricing Unit Size?

The problem I am running into is Sales Pricing Unit Size is a property and when working in Custom Mode it looks like I can only do calculations on available measures. Not sure if it would make sense to have Sales Pricing Unit Size as a measure, but I could look at changing it to a measure. Can it live as a property and a measure?

Thanks

I’ve had a similar situation before. The one value doesn’t make sense as a measure because it only really applies to one dimension (i.e. product) and doesn’t roll up to other dimensions well, (i.e. warehouse or customer).

I’ll start with your main question. No, you cant use the property in a calculation. Only dimensions.

Though you can’t use the exact same field in two places (property and dimension), you can get around this by creating a duplicate of the field. In the builder, add an “Expression”, and just set it the the field you are duplicating. Then you can use one as a property, and one as an measure.

But that brings us to what I mentioned above. Having this field as a measure may be a bit wonky with a lot of your dimensions, as it won’t roll-up quite right. Another better option may be to do the either as an Expression in the builder itself or back in Sync when you are syncing the source data. Sometimes it makes more sense to do this calculation then, back at the detailed level.

Just my 2 cents worth.

Thanks for the reply Aaron. One of the options you mentioned was to add an expression in the builder. If I wanted to try this, do I go into Designer, click on a dimension and then click the green plus icon to add a Transform column | Expression? That is the only place I found to add an Expression in Designer. When writing an expression, I am only able to choose fields from the dimension that I clicked on. I am still relatively new to Phocas and am learning as I go.

My invoice stream has the Quantity measure and I would like to divide that by a property (SalesPricingUnitSize) of my Item dimension.

If needed, I will perform the calculation in my SQL view, but I thought it would be beneficial to learn how to build expressions in the Designer.

Thanks,
Justin

There’s two place you can do it in designer. You can add a calculated measure using the “+” up beside the measures. You can also add a new column to the source data using the “+” down in the data preview at the bottom. (This gives you an option of adding an expression or an “If-Then” scenario.) You may have to play around with both to see what works best for your situation.

Hi @Justin, you can also create a custom property to perform the calculation. That kinda creates a custom column which has access to both properties and measures. See https://helpphocassoftware.atlassian.net/wiki/spaces/userdoc/pages/589996/Properties#Properties-Createacustomproperty

Hi @Will, thank you for the reply. I looked into this and I think the hangup I am having is the the measure is one stream and the property is in another.

In my Sales database, my Invoice stream has the measure of Quantity and my Item stream has the property SalesPricingUnitSize. What I would like to do is write an expression like this: Quantity / SalesPricingUnitSize.

When writing an Expression, am I able to use fields from multiple streams or can do they have to be from same stream?

Thanks,
Justin

Hi @Aaron, thanks for screenshot. That pointed me in the right direction. What I found when trying to write the expression is I could not see the properties and/or measures I need to pull into the expression.

In my reply to Will, I mentioned the property (SalesPricingUnitSize) I need to use in the expression is in my Item stream and the measure (Quantity) I need to use is in my Invoice stream.

Is there a way to write an expression pulling fields from different streams within the database?

Thanks,
Justin

Hi @Justin, sorry you won’t be able to use a transaction/stream property within a calculation. Only a dimension property.