Cumulative binomial distribution in equation editor

[demarest]

Hi, I am new to Qlik Sense and was not able to figure out how to calculate cumulative binomial distribution in the equation editor.  I would like to have this value in a table.

Qlik sense does not have discrete binomial distribution function either, but that can be calculated using existing functions as per below:

combin(n,x)*pow(0.05,x)*pow(0.95,n-x)

n and x are integers representing the number of observations and the number of statistical test exceedences at a 95% confidence interval.  The above formula works for discrete outcomes, for example, the probability of having exactly 5 out of 100 results be exceedences (n=100 and x=5 plugged into above), however, what I want is the cumulative probability of 5 or fewer exceedences, which would be the above with (n=100, x=0) + (n=100, x=1) + ... + (n=100, x=5).  It would seem like this could be achieved with a loop, but this does not seem to be available in the editor or an ability to create it in a custom function, something like:

cbd = 0

for i= 0 to x

cbd = cbd + combin(n,i)*pow(0.05,i)*pow(0.95,n-i)

next i

Any help with this would be appreciated

[sunny_talwar]

Would you be able to provide few rows of data with the numerical output you expect to see from it?

[lakshmikandh]

Please add sample data and sample output

[demarest]

Hi,  thanks for the responses.  Note that I did find a way to get what I want into the report, but it involved some very convoluted SQL involving functions from this post (Cumulative distributive functions for normal and binomial).  I did not handle this on the Qlik side, although I believe it should be possible, so I still would be appreciative if someone could show a way to do it in Qlik (given the data in the table below).  Here is a contrived example, two years of monthly return data, 25 total observations.  There is a measure called VaR which asserts that if the risk model is accurate, the absolute value of the monthly return should exceed VaR in only about 5% of cases. In this example, there are two observations where the realized return exceed the VaR amount.  The cumulative binomial distribution is a hypothesis test saying that the probability of having 2 or fewer exceedences out of 25 observations (given an accurate model) is 87%.  That is the number I am trying to get.  In Excel, it is one formula as per below, in SQL server it is involves coding as per above link.  In Qlik, it is possible to get a discrete probability or the probability of having exactly 2 exceedences:

combin(n,x)*pow(0.05,x)*pow(0.95,n-x)

where n=25 and x=2.  If it would possible to take the aggregation of this formula for x=0, x=1, x=2, then I would get my answer:

Hope that more clear and thanks in advance for any help.

[sunny_talwar]

Would you be able to attach the Excel file here?

Uploading a Sample

[demarest]

Hi Sunny,  sure, see attached.  I use random numbers for the returns in the example, so column D will change if you recalculate something or hit F9. Note that I am hard coding the 5% threshold, but the number of observations and exceedences will change for different funds / history used, so a solution would have to be flexible there.

[sunny_talwar]

I sort off understand the whole thing except these two numbers. What is the logic behind these two numbers?

[swuehl]

What about

=Sum( combin(25,ValueLoop(0,2))*pow(0.05,ValueLoop(0,2))*pow(0.95,25-ValueLoop(0,2)) )

?

[sunny_talwar]

Super!! that did seem to have worked

How exactly is Combin (Combination) function works here?

[swuehl]

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/GeneralNumericFun...