Expression that works in Calculated Column doesnot work in Calculated Value. Need help to resolve.

[Deepak Mathew]

Hello

I am attaching here my dxp . The problem is that a an Expression that seems to work in Calculated column doesnot seems to work(shows red) in Calculated Value in Text Area. I will give a brief explanation of the data .

part_id - Serial number of units

Lot - Batch number of units(there are 3 unique batches)

State - Description of treatment [Pre and Post ]

Parameter1 - Measured value of Parameter1

Parameter2 - Measured Value of Parameter2

Calculated column -StdDev(First(If([state]="Pre",[Parameter1])) over ([part_id],[Lot]) - First(If([state]="Post",[Parameter1])) over ([part_id],[Lot]))

This formula is simply trying to calculate stdev of variation in Parameter1 between each part from Pre to Post

When I apply the same Expression in Calculated Value in the Text Area it shows red and doesn't work. Please help me to understand and resolve.

Deepak

[Fabian Duerr]

It works fine if you first create a calculated column [yourCol]:

First(If([state]="Pre",[Parameter1])) over ([part_id],[Lot]) - First(If([state]="Post",[Parameter1])) over ([part_id],[Lot])

And then make your calculated value:

StdDev([yourCol])

But I can't tell you why... But I had this issue several times and the calculated column is my solution.

[Deepak Mathew]

Understood, I also thought about this , but the problem is I have multiple paramter columns and I was trying to do was dynamically calculate the value using "Column from Marked". So when I select the column name  in a table automatically this value gets calculated.

 

Is there way to do "Column from Marked" in claculated Columns. That would be enough for me.

 

Deepak

 

 

[Fabian Duerr]

I don't know if this is possible with column from marked...

 

But if you could work with a document property that holds your column selection it should work well. You could also set you axis in a visualization with this property.If you could share a more detailled example file I can have a closer look.

[Deepak Mathew]

Hi

I will try to explain my use case in better way . Typically what I do is do an ANOVA on the Parameter1 , Parameter2 with State in X column. Then based on what is selected/ marked in the Data Relationship column I wanted the Calculated Value to give Stdev for Deviations between unique devices between Pre an Post.

[Fabian Duerr]

Sorry for my late reply. I checked your file and I understand what you want to to. But I don't know how you can use the "marked column" information. But probably you can use a script to access and use this information.

In the attached file you will find the workaround solution by using a document poperty for a column selection.

I also found that some of your "post" rows have duplicates which leads to a false result as the rows are used twice in the StdDev calculation. Please check my Pivot Table (page3) to see the difference.