8 Replies Latest reply: Sep 30, 2015 9:47 AM by Rayleigh Silver RSS

    Score-Formula based on Avg and Stdev

    Rayleigh Silver

      Hello,

       

      I am new and not sure if this is the right section.


      First of all I have some variables. For example households, income, etc.

      For each of this variables i need a score based on a model. I already have the following in MS Excel 2010:

       

      AreaValueAvgStdevDifferenceRatioScore 1Score 2
      120255-5 (value-avg)-1 (diff/stdev)-33,3 (ratio/3 *100)66,7 (score1 +100)
      2302555133,3133,3

       

      Now i need this in Qlik Sense.

      What I tried is the formula: ((((Sum([Households])-Avg([Households]))/stdev([Households]))/3)*100)+100

      But it did not work.

       

      I hope i could make the problem clear.

      I appreciate your help. Thank you!

        • Re: Score-Formula based on Avg and Stdev
          Rayleigh Silver

          I am not sure how this works, but because nobody answered my question i will just try to pick this right up to the top. Thank you!

          • Re: Score-Formula based on Avg and Stdev
            Stefan Wühl

            'it did not work' is not a good issue description. Could you be more specific?

             

            And it would really help if you could post some sample lines of data.

             

            I can only guess that you may want something like

             

            Area Sum(Value) avg(Total Value) Stdev(TOTAL Value)* sqrt(Count(TOTAL DISTINCT Area)-1)/sqrt(Count(TOTAL DISTINCT Area)) =Column(1) - column(2) column(4)/column(3) column(5)/3*100 column(6)+100
            50 25 5 25 5 166,67 266,67
            120255-5-1-33,3366,67
            2302555133,33133,33
              • Re: Score-Formula based on Avg and Stdev
                Rayleigh Silver

                Hello swuehl,

                 

                thank you for the answer.

                I actually do not know why it does not work.

                If I try the formula that i posted ((((Sum([Households])-Avg([Households]))/stdev([Households]))/3)*100)+100))) the columns are empty.

                 

                I copied some lines from the excel-sheet i exported from qlik.

                For each of these columns i need the model.

                      

                PostalSum(DB1)Sum(Bonus)Sum(Taxes)Sum(Costumers)Sum(Households)Sum(Income)
                639282162566,614310838,17722078524,3560,78110144853,00
                647543056356,155483958,01082892561,611,6633840581,00
                681591019263,95159013,01621120686472,661224528861,00
                681611034790,483160582,62941134150436,221033727050,00
                681631036322,055162123,04451142232741,851378939815,00
                681651036663,807162165,25941142232770,221370533689,00
                681671036487,858162143,88981142232661,631517529636,00
                681691036584,126162159,50311142232581,671530733812,00
                681991036559,996162157,28811142232806,201547437699,00
                682191016058,05159002,93321117608591,081226341704,00
                682291036634,605162163,37671142232121,80281338802,00
                682391036538,232162153,28721142232488,19761644973,00
                682591016144,769159010,20631117608662,361174447258,00
                683051036646,878162164,91881142232598,661489243764,00
                683071036275,559162117,64931142232476,631295241185,00
                683091039115,906162193,37281145310754,601629840529,00
                685261096445,126162245,00481037600,85340,11580449631,00
                685351161548,268191326,72711107741,7382,54704545037,00
                685421987983,326297278,72331825882,5341,63581047356,00
                685491069875,521153714,38331051773,9234,50374657277,00
                687233283286,237481541,36183101026,51164,762094949502,00
                687531098452,651161628,58951038909,3516,62956747150,00
                68766960702,6988149750,4572900430,5555,451013648140,00
                687751112355,955154809,79291094627,4361,90622949718,00
                687821090272,798162521,31711031058,6420,98734747327,00
                687891092671,205162161,59511032367,05350,96605148852,00
                687941095015,074162320,48011034983,95220,49429847787,00
                687991098817,209162347,09251038909,3180,78335451001,00
                688041090046,31162516,57691031058,6141,74249150058,00
                688091090467,688162532,48451031058,6180,02307248910,00
                69115988960,1067157794,21381028481771,181296139656,00
                69117990588,5081159349,59191036203393,40710136944,00
                69118969817,8554156222,29781012731474,79688148997,00
                  • Re: Score-Formula based on Avg and Stdev
                    Stefan Wühl

                    Have you tried with the expressions I posted above and used in my sample QVW?

                     

                    I think you need to use the TOTAL qualifier on some aggregations (e.g. when you want to calculate the average across all areas).

                     

                    If you need to calculate an average of aggregated (i.e. sum'med) values, you need to use advanced aggregation (the aggr() function).

                     

                    Could you upload some lines of your raw data for Households, maybe only for some areas, and add your requested outcome?

                      • Re: Score-Formula based on Avg and Stdev
                        Rayleigh Silver

                        I do not know how to use the qvw-file, so I am still searching for that.

                         

                        I will try the total qualifier. Thank you!

                         

                        Here is a sample: The Households raw data and the steps i did in Excel. I hope this is what you mean or need.

                        Thank you!

                             

                        Postal

                        Value

                        (Households Sample)

                        AVG (Sample)STDEV (Sample)Difference (Value,AVG)

                        Ratio

                        (Difference, STDEV)

                        (Ratio *100)

                        /3

                        Score!

                        ((Ratio*100/3)

                        +100)

                        6475440581,0043208,527175,86-2627,52-0,37-12,2187,79
                        6815928861,0043208,527175,86-14347,52-2,00-66,6533,35
                        6816127050,0043208,527175,86-16158,52-2,25-75,0624,94
                        6816339815,0043208,527175,86-3393,52-0,47-15,7684,24
                        6816533689,0043208,527175,86-9519,52-1,33-44,2255,78
                        6816729636,0043208,527175,86-13572,52-1,89-63,0536,95
                        6816933812,0043208,527175,86-9396,52-1,31-43,6556,35
                        6819937699,0043208,527175,86-5509,52-0,77-25,5974,41
                        6821941704,0043208,527175,86-1504,52-0,21-6,9993,01
                        6822938802,0043208,527175,86-4406,52-0,61-20,4779,53
                        6823944973,0043208,527175,861764,480,258,20108,20
                        6825947258,0043208,527175,864049,480,5618,81118,81
                        6830543764,0043208,527175,86555,480,082,58102,58
                        6830741185,0043208,527175,86-2023,52-0,28-9,4090,60
                        6830940529,0043208,527175,86-2679,52-0,37-12,4587,55
                        6852649631,0043208,527175,866422,480,9029,83129,83
                        6853545037,0043208,527175,861828,480,258,49108,49
                        6854247356,0043208,527175,864147,480,5819,27119,27
                        6854957277,0043208,527175,8614068,481,9665,35165,35
                        6872349502,0043208,527175,866293,480,8829,23129,23
                        6875347150,0043208,527175,863941,480,5518,31118,31
                        6876648140,0043208,527175,864931,480,6922,91122,91
                        6877549718,0043208,527175,866509,480,9130,24130,24
                        6878247327,0043208,527175,864118,480,5719,13119,13
                        6878948852,0043208,527175,865643,480,7926,22126,22
                        6879447787,0043208,527175,864578,480,6421,27121,27
                        6879951001,0043208,527175,867792,481,0936,20136,20
                        6880450058,0043208,527175,866849,480,9531,82131,82
                          • Re: Score-Formula based on Avg and Stdev
                            Jonathan Dienst

                            To do this in script:

                             

                            T1:

                            LOAD * Inline

                            [

                            Postal, Value

                            64754, 40581.00

                            68159, 28861.00

                            68161, 27050.00

                            68163, 39815.00

                            68165, 33689.00

                            68167, 29636.00

                            68169, 33812.00

                            68199, 37699.00

                            68219, 41704.00

                            68229, 38802.00

                            68239, 44973.00

                            68259, 47258.00

                            68305, 43764.00

                            68307, 41185.00

                            68309, 40529.00

                            68526, 49631.00

                            68535, 45037.00

                            68542, 47356.00

                            68549, 57277.00

                            68723, 49502.00

                            68753, 47150.00

                            68766, 48140.00

                            68775, 49718.00

                            68782, 47327.00

                            68789, 48852.00

                            68794, 47787.00

                            68799, 51001.00

                            68804, 50058.00

                            ];

                             

                            Join (T1)

                            LOAD Avg(Value) As Avg,

                              Stdev(Value) As Stdev

                            Resident T1;

                             

                            T2:

                            LOAD *,

                              (Ratio * 100 / 3) + 100 As Score;

                            LOAD *,

                              Difference / Stdev As Ratio;

                            LOAD Postal,

                              Value,

                              Avg,

                              Stdev,

                              Value - Avg As Difference

                            Resident T1;

                             

                            DROP Table T1;

                            • Re: Score-Formula based on Avg and Stdev
                              Jonathan Dienst

                              To do in the front end, create a straight table with Postal as dimension, and then the following expressions:

                               

                              =Sum(Value)

                              =Avg(TOTAL Aggr(Sum(Value), Postal))

                              =Stdev(TOTAL Aggr(Sum(Value), Postal))

                              =Sum(Value) - Avg(TOTAL Aggr(Sum(Value), Postal))

                              =(Sum(Value)  - Avg(TOTAL Aggr(Sum(Value), Postal))) /  Stdev(TOTAL Aggr(Sum(Value), Postal))

                              =(((Sum(Value)  - Avg(TOTAL Aggr(Sum(Value), Postal))) /  Stdev(TOTAL Aggr(Sum(Value), Postal))) * 100/3) + 100