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 
Accepted Solutions
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.
I sort off understand the whole thing except these two numbers. What is the logic behind these two numbers?
Super!! that did seem to have worked
How exactly is Combin (Combination) function works here?
