Need Help with Expression. Urgent.

[snarrp_Rocky]

Dear All,

I am stuck with one important project with this result, would be grateful is someone kindly help me get this results please. I need to submit the pack within 5 days.

I have attached excel (Data and Result) file with data and outcome or result with formula how I would like to be seen in qlik view.

Could you please help with sample QVW file.

Kind Regards,
Nitin

[sunny_talwar]

Try this

=Min(Aggr(Sum(PnL), ScenarioDateID), 5)

[albert_guito]

If you want to see the 5th lower results in you pivot table you can also use the dimension

=AGGR(IF(Rank(sum(PnL), 4)<=5, varRank), varRank)

[snarrp_Rocky]

This is Great. Thanks Sunny so much. I have one constraint here. If I add another desk, it is not giving me the result.

I have attached what I am referring to. Your further help would be great help.

Kind Regards

Nitin

[snarrp_Rocky]

Thanks Albert But this expression is not giving me the result. I have some expression written. Got help from Sunny. Just need to get some further help.

I have attached my QVW and I have mentioned in the test box what I am looking for please.

Kind Regards

Nitin

[sunny_talwar]

Try this

=Min(Aggr(Sum(PnL), ScenarioDateID, NodeName5), 5)

[snarrp_Rocky]

thank you Sunny. that was really helpful.

Would you mind helping with how do I get the scenario date of the 5th min which we calculated. I need to get this populated in right next column of this calculated number.

Many thanks for your kind help!

Regards

Nitin

[sunny_talwar]

Try this

=FirstSortedValue(ScenarioDateID, -Aggr(Sum(PnL), ScenarioDateID, NodeName5), 5)

[snarrp_Rocky]

Thanks Sunny. But the date for Credit & DCM should be 16th Mar 2017, and IRO & CPI = 4th Sep 2017

[sunny_talwar]

My bad, try this

=Aggr(If(Sum(PnL) = Min(TOTAL <NodeName5> Aggr(Sum(PnL), ScenarioDateID, NodeName5), 5), ScenarioDateID), ScenarioDateID, NodeName5)