Creating Custom Expression for individual averages...

[Greg App]

Hello-

I am trying to create a custom expression (I believe it would involve an "OVER" function) for a file I am working on.I have a table with 4 different AFE job names: Apple, Banana, Carrot and Duck. These job names are assigned to two different item IDs (T and IT).Each of these 4 different AFE Job names have an assigned Length.

I have calculated the # per Item ID for a particular AFE # (Sum([Amount]) OVER ([Job ID - New AFE],[item ID])

I have also calculated the $ per Item ID for a particular AFE# per Foot ([$ per Item ID for a particular AFE#] / [Length (ft)]

However, I need a way to find the the average of the differentvalues associated with T, as well as with I. However, not ALL values...just different values.

For example, if several listed values associated with T are 5,5,5,4,4,3,3,2 and 1, I don't want to find the average of ALL these numbers. Just the average of 5,4,3,2 and 1, which is 3.

I have attached file to this question for clarification. Pleaes let me know if I need to offer any more clarification.

[Shashank Gurnalkar]

Try the following expression and see if it helps. I have calculated for [$/ft/AFE #] column.

Avg(If(DenseRank(RowId(),"Asc",[$/ft/AFE #],[item ID])=1,[$/ft/AFE #],NULL)) OVER ([item ID])

[Greg App]

This seems to work, bt it also seems to ignore any filters or "drill downs" that I apply.  Is there a way to fix that  Thanks for your help

[Shashank Gurnalkar]

 A calculated column is treated like any other column and its contents are static during all further analysis.

 

Reference:  https://docs.tibco.com/pub/sfire-analyst/10.3.3/doc/html/en-US/TIB_sfire-analyst_UsersGuide/ncfe/ncfe_what_is_a_calculated_column_.htm

[Greg App]

Sorry, that was a stupid question on my part, I knew that.

 

Perhaps it would be easier if I simply stated what I am trying to accomplish.  

 

I am trying to find the average $/ft for a given categry (Tangible and Intangible), with this $ value being able to change depending on filters and/or drill downs. 

 

The graph on the bottom shows I am trying to accomplish this...however, after checking them by hand, I see they are off (I didn't take order of operations into account...oops).

 

Basically, I would need the sum of all "Amounts" for a particualar "Item ID" divided by the sum of all unique "Length" values for a particular "Item ID".

 

My graph works when I have a single "Job Name-AFE" category selected, but it then falls apart when I have multiple selected (due to order of operations)

 

This would have to be a dynamic value that would change depending on filters 

 

(Column names have been placed in Quotations)

 

Any help would be greatly appreciated.