Conditional Dimension to hide rows below a threshold

brandonD14 — Sat, 16 Nov 2024 03:32:36 GMT

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!!

topic Conditional Dimension to hide rows below a threshold in QlikView

Conditional Dimension to hide rows below a threshold