topic Re: Need Help with Expression. Urgent. in QlikView

Need Help with Expression. Urgent.

snarrp_Rocky — Mon, 26 Jan 2026 18:19:17 GMT

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

Re: Need Help with Expression. Urgent.

sunny_talwar — Thu, 24 Jan 2019 12:29:57 GMT

Try this

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

Re: Need Help with Expression. Urgent.

albert_guito — Thu, 24 Jan 2019 12:42:48 GMT

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)

Re: Need Help with Expression. Urgent.

snarrp_Rocky — Thu, 24 Jan 2019 14:24:41 GMT

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

Re: Need Help with Expression. Urgent.

snarrp_Rocky — Thu, 24 Jan 2019 14:26:58 GMT

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

Re: Need Help with Expression. Urgent.

sunny_talwar — Thu, 24 Jan 2019 14:39:24 GMT

Try this

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

Re: Need Help with Expression. Urgent.

snarrp_Rocky — Fri, 25 Jan 2019 11:40:54 GMT

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

Re: Need Help with Expression. Urgent.

sunny_talwar — Fri, 25 Jan 2019 12:06:29 GMT

Try this

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

Re: Need Help with Expression. Urgent.

snarrp_Rocky — Fri, 25 Jan 2019 12:29:37 GMT

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

Re: Need Help with Expression. Urgent.

sunny_talwar — Fri, 25 Jan 2019 12:46:01 GMT

My bad, try this

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

Re: Need Help with Expression. Urgent.

snarrp_Rocky — Fri, 25 Jan 2019 15:19:00 GMT

Re: Need Help with Expression. Urgent.

sunny_talwar — Fri, 25 Jan 2019 12:52:28 GMT

20170316 = 16th March 2017

20170904 = 4th Sep 2017

What is the issue?

Re: Need Help with Expression. Urgent.

snarrp_Rocky — Fri, 25 Jan 2019 13:09:15 GMT

This worked. I owe you one 🙂

Last but not the least for today please....

If I have to take average of 1st min to 13th min, how can I do that.

I am try with this expression but not getting it

=Avg({<varRank={1,2,3,4,5,6,7,8,9,10,11,12,13}>}PnL)

Re: Need Help with Expression. Urgent.

sunny_talwar — Fri, 25 Jan 2019 13:15:01 GMT

What is varRank here? Created in script?

Re: Need Help with Expression. Urgent.

snarrp_Rocky — Fri, 25 Jan 2019 13:17:08 GMT

you can ignore VaR Rank, it is the 5th min at each portfolio level, ideally what I am trying to achieve here is the earlier 5th min we calculated, like that we need to calculate from 1st min to 13th and take average of that..

Re: Need Help with Expression. Urgent.

sunny_talwar — Fri, 25 Jan 2019 13:22:03 GMT

Try this

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

Re: Need Help with Expression. Urgent.

snarrp_Rocky — Fri, 25 Jan 2019 13:26:00 GMT

this is cool. Just one thing, Can it be possible to shw previous days number in next column. Just ping the code pls. And huge thanks for your kind support....

Re: Need Help with Expression. Urgent.

snarrp_Rocky — Fri, 25 Jan 2019 15:20:25 GMT

Re: Need Help with Expression. Urgent.

sunny_talwar — Fri, 25 Jan 2019 14:05:43 GMT

We have already tried to resolved multiple issues here, would you be able to create a new thread for this additional requirement?

Thanks,
Sunny