Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have been racking my brain and the scouring the forums to help solve my issue...but i have not yet figured this out.
I have created a pivot table consisting of a few expressions calculating an average value of the most recent data response of a user parameter(ID). The average takes into account minimum criteria that a user can change on the fly to eliminate data points that are below certain thresholds. My expression looks like:
Column 1: avg({<A= {">=$(vminA)"}, B= {">=$(vminB)"},C= {">=$(vminC"} >} aggr(FirstSortedValue(CAT_Comppct,-Date),ID))
Column 2: avg({<A= {">=$(vminA)"}, B= {">=$(vminB)"},C= {">=$(vminC"} >} aggr(FirstSortedValue(CAB_Comppct,-Date),ID))
Column 3: avg({<A= {">=$(vminA)"}, B= {">=$(vminB)"},C= {">=$(vminC"} >} aggr(FirstSortedValue(CAP_Comppct,-Date),ID))
This part is working well and all the columns calculate what i want based on the user input boxes.
Now i am trying to create a summary table (on another tab) that will only show rows of data where any of columns of the pivot table are less than another user entered variable, vminAVG.
I have tried using the above expression as a conditional dimension, which i wrapped in an AGGR(x, ID) function sorted (again) by ID, but the results do not match what i am expecting and seem to update when i change the vminAVG value.
Conditional Dimension (suppressing Null):
=aggr(IF(avg({<A= {">=$(vminA)"}, B= {">=$(vminB)"},C= {">=$(vminC"} >} aggr(FirstSortedValue(CAT_Comppct,-Date),ID)) >vminAVG,"",1),ID)
I have also tried putting the aggr() inside the IF()...no luck
So far, i have only been trying to get the summary table to work using the conditional on just one column because i figured that if i could figure out how one works, i can get the other to follow.
Thanks in advance for any help!!