Confused about formulas

#1

I have a dashboard that was built by a Dundas consultant that I am now looking to modify in a few important ways. One of those ways is to add weighting to some measures that we are reporting on, so that their relative importance can be conveyed.

Right now there is a datalabel on the dashboard, the text of which comes from a formula that looks like this:

($dataLabelAuditCompliancePercent.Audit Compliance.Compliance%$ + $dataLabelAvgDaysToClosePercentCS.Customer Satisfaction.% Days To Close$)/2

As you can see, it’s taking the value of measures in two other visualisations and doing some trivial maths on them (basically, to derive the percentage score for this part of the dashboard we’re taking the simple average of the scores for the constituent visualisations of this part of the dashboard. So far, so simple.

What we want to do now is modify this so that each constituent score can be weighted individually, and instead of taking the average of all the scores, we simply add up the weighted scores. The formula I want to use looks like this:

(($dataLabelAuditCompliancePercent.Audit Compliance.Compliance%$ * $dataLabelAuditCompliancePercent.Audit Compliance.AuditComplianceWeighting$) + ($dataLabelAvgDaysToClosePercentCS.Customer Satisfaction.% Days To Close$ * $dataLabelAvgDaysToClosePercentCS.Customer Satisfaction.PercentDaysToCloseWeighting$))

All I have done is to add another measure to the two visualisations, which is the weighting for the visualisation.

The problem is that getting the value of these measures does not work. I can see their values in the visualisation if I select Data Preview, and those are correct, but when used in the formula they are always 0. What’s worse, if I try to manually recreate the existing formula, the value of ($dataLabelAuditCompliancePercent.Audit Compliance.Compliance%$ is also 0. I’ve literally copied and pasted it, and it works fine in one datalabel, but not in another.

This is the first time I’ve attempted to use values from another datalabel in a formula, so there’s a good chance I’ve missed a step somewhere, but it’s not at all clear to me what that could be.

Can anyone see where I’m going wrong?

Adam

#2

I have had similar problems with formula visualisations. They don’t seem to like adding measures after they have been set up.

Try creating a new formula visualisation data label, and in the formula don’t type the other data label names. Click in the formula bar as if you are going to type, but instead click on the other labels to get them into the formula.

That may help you, if not then I’m out of ideas…

#3

Great, thanks, I’ll give that a go. Glad I’m not the only one who’s seen this. It was driving me mad earlier.

#4

It’s odd… I can click on some visualisations, and the measure will get added to the formula, but others do nothing. The one I wanted to use is one of the ones that does nothing, unfortunately. When I can click on a visualisation, the formula visualisation does work correctly, which I suppose is progress. I’m currently trying to decide if creating new data labels just for the data I want to use and hiding them off screen is worthwhile or ridiculous.

#5

You won’t need to keep those data labels off the screen: Once the formula metric set has been created, it is safe to delete the original visualization and metric set from your dashboard or view, if you choose. The original metric set is used only to define the formula, and the new metric set is independent.

In terms of adding elements to a formula visualization after it was created: You can add any elements that are missing from the formula metric set, as long as they were already selected in the original metric set(s).

Note that while you can’t use the click to add links, you can drag the same elements from the Explore window that were originally added to the original metric set, and drop them onto the Data Analysis Panel of the new formula metric set. They must be in the same place they were added in the original metric set (e.g., under Rows, if originally added there).

We have recently updated our documentation in regards to formula visualization - hopefully it clarifies it all: using a formula visualization

#6

Thanks, I’ll take a look at the docs. You’ve explained why I couldn’t add stuff to the current visualisation, thanks for that too. It at least makes sense now. I’ll give it a go with a brand new visualisation and see where I get to.