Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Score-Formula based on Avg and Stdev

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!

8 Replies
Not applicable

Re: Score-Formula based on Avg and Stdev

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!

MVP
MVP

Re: Score-Formula based on Avg and Stdev

'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
Not applicable

Re: Score-Formula based on Avg and Stdev

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
MVP
MVP

Re: Score-Formula based on Avg and Stdev

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?

Not applicable

Re: Score-Formula based on Avg and Stdev

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
MVP
MVP

Re: Score-Formula based on Avg and Stdev

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;

MVP
MVP

Re: Score-Formula based on Avg and Stdev

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

Not applicable

Re: Score-Formula based on Avg and Stdev

Hello Jonathan,

thank you.

I am really not good with these things, i do not know if you mean like this:

Bildschirmfoto 2015-09-30 um 15.44.27.png

"Anzahl Haushalte" = Households (Value)

"PLZ" = Postal

The notification says: Garbage after expression "="

Community Browser