topic Re: Calculate a score based on a matrix with dimensions and conditions in App Development

Calculate a score based on a matrix with dimensions and conditions

MrBosch — Sat, 20 Aug 2022 09:35:08 GMT

Hi all

I have a challenge and a long list of different kind of dimensions (age, sex, ...). Depending their values a score should be looked-up and added to a final score.

I have looked at the existing matrix in Excel and created a database containing the 'conditions' for all dimensions.

So the first is easy: if the subject is a male add 1 point to the score (if it is a female add 5).
Now the 'young male value' (which is read from another database) needs to be evaluated against the conditions in the series ref-val-ymv-1 to 9. So let it be 5,5 for now: it will not match against the condition ref-val-ymv-0 [<3,5] (add 0) but the condition in ref-val-ymv-4 and therefore add 4 to the total score.

So...
SUM(
If sex=male -> score = 1
If sex=female -> score = 5
If youngmalevalue = 5,5 -> score = 4
... )
To get to the final score.

The example above is an abbreviated list. The number of dimensions and their 'lookup'-conditions is rather limited but the original will contain about 70 rows and 20 columns therefore making it painstaking to evaluate all dimensions one by one creating formulas and even then adding it up to some final score.

Hope someone can help me.

Alex

Re: Calculate a score based on a matrix with dimensions and conditions

Mark_Little — Thu, 15 Sep 2022 06:18:02 GMT

Hi @MrBosch

I would personally look at the scripting options for this, you have the data. First thoughts from me, load left join the scores to your fact table. Then resident load, the key and scores for the fact table and drops the scores from the fact table. We now have your fact table and table. Next crosstab the scores table you three fields Key, Score type and score.

Then front end you can just SUM(Score), or limit using set expressions {<[Score Type]={'Age'}>}

Hope this makes sense and helps

Re: Calculate a score based on a matrix with dimensions and conditions

EdgarOlmos — Thu, 15 Sep 2022 16:03:07 GMT

Hi.
An option it's to solve the problem using the script editor.
You can make a mapping table like this:

After that, in the load of the transactional data with an applymap you can find the value that you need.

Regards

Re: Calculate a score based on a matrix with dimensions and conditions

MrBosch — Sat, 17 Sep 2022 18:57:32 GMT

Thank you so much. That will do the trick!