topic Re: aggr function in QlikView

aggr function

Anonymous — Mon, 19 Sep 2016 06:03:35 GMT

Hello all,

I have a straight table, the dimension contains the calculated dimension of ValueLoop(100,150) and the expression is quite complicated like this :

=(Sum( {$<[Value Date] = {"<=$(v_MaxDate)"}>}
If (PC = 'Put' AND ValueLoop(100,150) -[Expiration Index] < 0,
((ValueLoop(100,150) - [Expiration Index]) * [Quantity] * -[Factor]),
If (PC = 'Call' AND ValueLoop(100,150) -[Expiration Index] > 0,
((ValueLoop(100,150) - [Expiration Index]) * [Quantity] * [Factor])))) - Sum ([Premium]))

And it works file.

I need to display the minimum value and the maximum value of the list of values displayed in the chart. These 2 numbers should be displayed in 2 variables outside the chart because I need a further calculation on these 2 numbers and only then, they will be the actual output.

Actually, the Min value should be from 15% from the beginning of the list and the Max value should be from 15% from the end of the list of values.

Can someone help me with this?

Re: aggr function

Anil_Babu_Samineni — Mon, 19 Sep 2016 17:55:22 GMT

This is not much complicated,

Would you provide this

What is the Value of this -- v_MaxDate

LET VExpression = ValueLoop(100,150) -[Expiration Index]

Perhaps This

=(Sum({$<[Value Date] = {"<=$(v_MaxDate)"}>},
If (PC = 'Put' AND (ValueLoop(100,150) -[Expiration Index]) < 0,
((ValueLoop(100,150) - [Expiration Index]) * [Quantity] * (-[Factor])),
If (PC = 'Call' AND (ValueLoop(100,150) -[Expiration Index]) > 0,
((ValueLoop(100,150) - [Expiration Index]) * [Quantity] * [Factor])))) - Sum ([Premium]))

Sum({$<[Value Date] = {"<=$(v_MaxDate)"}, PC={'Put','Call'}, If(VExpression<0, ($(VExpression) * Quantity * (-Factor)), ($(VExpression) * Quantity * Factor) >} Premium) - Sum(Premium)

Re: aggr function

sunny_talwar — Tue, 20 Sep 2016 01:51:30 GMT

Is it possible to share a sample where you have been playing around with it? May be it is easier to see what you have and take it forward from there?

QlikCommunity Tip: How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: aggr function

Digvijay_Singh — Tue, 20 Sep 2016 04:28:02 GMT

the last requirement is bit confusing related to 15% from beginning and 15% from end, not sure if you need your resulted values in sorted order. Do you mean if you have 100 values then you want to calculate min and max between 15th to 85th value or something else?

Re: aggr function

Anonymous — Tue, 20 Sep 2016 04:42:20 GMT

Thanks for your answer,

Maybe a made it too complicated, see the attached print screen. I made a simple scenario and as you can see, the results should be 4 figures.

Many thanks

Re: aggr function

sunny_talwar — Tue, 20 Sep 2016 19:10:37 GMT

Would you be able to share the qvw with us?

Re: aggr function

Anonymous — Wed, 21 Sep 2016 09:05:11 GMT

Yes, Please pay attantion that the expression is just an example, in real life i will use a different one. the idea is to create expression in a text object that simulate a value in a chart.

many thanks

Re: aggr function

sunny_talwar — Thu, 22 Sep 2016 00:09:30 GMT

I see the issue now. I think Aggr() only pre-created dimensions which are available in the script. Is it possible to create the ValueLoop() in the script?

Data:

LOAD 49 + IterNo() as ValueLoopDim,

0.5 * Rand() as Rand

AutoGenerate 1

While IterNo() <= 101;