Show Part (as a dimension) corresponding to Min Value in a Pivot/Straight Table

[mborsadw]

In the 2nd straight table, I want to display the dates and the Min Value across all parts for that date and the Part corresponding to the Min Value for that date.

I am able to do it as shown above but the Parts are displayed as Measures and thus are not selectable. I would like to click on a particular part in the table (highlighted in yellow) thus filtering the dataset to that part.

Is this possible?

QVF is attached.

[asinha1991]

create  a dimension as below

=aggr(firstsortedvalue(Part, Value), Dt)

[mborsadw]

I do see the part numbers as dimensions with your approach. But when I select the part number, it makes a selection on Dates as seen in the above screenshot.

[asinha1991]

This selects both dt and part

=if(aggr({1}rank(-sum(Value)),Dt,Part)=1,Part)

hopefully there is a better way

[mborsadw]

@sunny_talwar any other approach?

[karthiksrqv · Accepted solution]

Hi,

If in the straight table, we use Dt and Part as dimensions and

"

if(
sum(Value)=
min(Total <Dt> aggr(sum(Value),Dt,Part,Group)),
sum(Value),
0
)

" as the measure with suppress zero value. Would it work?

[shdhara_zyme]

Hi,

Have incorporated the logic in the load script, hope this helps

Regards

Shashi