"Fixed calculation" using dimensions

#1

I’m working to duplicate a calculation that’s currently being done in Tableau, using Tableau’s “fixed calculation” feature.

Previously I posted in the forum and with help was able to replicate the “fixed calculation” using Dundas’ function properites. An example is " SUM($unitWeight$,$calendarDate$,$userName$) " , which sums $unitWeight$ while holding $calendarDate$ and $userName$ fixed.

Now I want to do a similar calculation to replicate the Tableau calculation below:

image
image.png1038×249 34.8 KB

The issue I’m having is that I don’t know how to tell Dundas to only sum (for example) $AgentStateTimeInSecs$ when $AgentState$ is “loginTimeInSecs”.

Dundas won’t allow me to bring dimensions into functions, and $AgentState$ is a dimension.

Is this a problem that can only be solved by modifying the data cube? Or can it be done in a metric set?

Thanks for the help, I’m finding the forum very useful!

1 Like
#2

Hi Thomas,

You could write a formula using the .Caption member of the hierarchies.

Your formula would be something like this
if ($AgentState$.Caption == “loginTimeInSecs”) {SUM($AgentStateTimeInSecs$)}

If you have two fixed values you could use OR(||) or AND(&&) operator to specify the second fixed value before the SUM operation.

Additionally you could refer to this support article on adding formulas https://www.dundas.com/support/learning/documentation/analyze-data/formulas/adding-formulas#h3-2-placeholders

Hope that answers your question.

2 Likes
#3

Satya,

Thanks, that’s very helpful!

I don’t have a C## background, so much of the scripting and formulas are new to me.

1 Like
#4

You’re welcome, Thomas. Happy to have helped :slight_smile:

1 Like