topic Re: Triangular distribution function in App Development

Triangular distribution function

FlyingFrog — Fri, 28 Apr 2023 22:13:20 GMT

Hello,

I am looking for a way to draw values from a triangular distribution function. However, such function does not exist in the statistical functions available in Qlik Sense.

I thought of coding the function myself, but I am facing difficulty writing it.

The idea was to have a function (e.g. TriangDraw) that requires the Lowest bound, Mode and Highest bound of the triangle as input.

Thank you in advance!

Re: Triangular distribution function

FlyingFrog — Fri, 28 Apr 2023 22:16:02 GMT

@hic As I saw your post on monte carlo methods in QLik, possibly you know an answer to this.
Once the function is completed I am aiming to code monte carlo simulations for risk management and visualize results in Qlik sense :).

Re: Triangular distribution function

hic — Sat, 29 Apr 2023 08:55:39 GMT

I have no experience in this, but I found these expressions on Wikipedia:

I think you can create what you need from this. U is the same as the Qlik Rand() function. a/b are lower/upper bounds and c is the mode.

https://en.wikipedia.org/wiki/Triangular_distribution

Re: Triangular distribution function

FlyingFrog — Mon, 01 May 2023 09:46:06 GMT

Hi Hic,

Thank you I will further explore. Will post my solution here if succesful.

If anyone else still able to assist -> thank you!

Re: Triangular distribution function

MarcoWedel — Mon, 01 May 2023 19:43:07 GMT

maybe helpful:

LET a = 1; LET b = 4; LET c = 3; LET Fc = (c-a)/(b-a); SET X = If($1<$(Fc),$(a)+sqrt($1*($(b)-$(a))*($(c)-$(a))),$(b)-sqrt((1-$1)*($(b)-$(a))*($(b)-$(c)))); table1: LOAD *, $(X(U)) as x; LOAD RecNo() as ID, Rand() as U AutoGenerate 10000000;

Re: Triangular distribution function

FlyingFrog — Tue, 02 May 2023 09:24:08 GMT

Thank you Marco!

Was getting to something similar, but I think your code runs faster and will be more feasible to applying X to a table with a number of e.g. 10 different triangular distribution characteristics.

Lets see what today is going to bring me to :)!

Thanks again.

Re: Triangular distribution function

FlyingFrog — Tue, 02 May 2023 12:14:50 GMT

For those looking for a similar solution to iterate similar items to perform e.g. a monte carlo simulation on a number of triangular distributions one can use the following code:

Let Iteration = 10000;

//Create a table with a number of distribution characteristics
ExampleDist:
LOAD * inline 
[
ID,a,b,c,Prob
123,100,800,200,0.5
124,50,600,500,0.3
125,600,800,700,0.8
];

// Loop through each row of the ExampleRisks table
FOR i = 0 to NoOfRows('ExampleDist')-1
    LET a = Peek('a', i, 'ExampleDist');
    LET b = Peek('b', i, 'ExampleDist');
    LET c = Peek('c', i, 'ExampleDist');
    LET ID = Peek('ID', i, 'ExampleDist');
    LET Prob = Peek('Prob', i, 'ExampleDist');
    SET Fc = ($(c)-$(a))/($(b)-$(a));
SET X = If($1<$(Fc),$(a)+sqrt($1*($(b)-$(a))*($(c)-$(a))),$(b)-sqrt((1-$1)*($(b)-$(a))*($(b)-$(c))));

//Store 
 Samples:
LOAD *,
    $(ID) as ID,
$(Prob) > Rand() as occured,
      $(X(U)) as x;
LOAD RecNo() as Line,
         Rand() as U
AutoGenerate $(Iteration);

NEXT i
;

All drawn values in one figure: