Calculated score_company

Question & Answer
,
1

I calculate a score in this way:

raw_score = {Z-score of page_view} * 8 +
{Z-score of submission} * 9 +
{Z-score of open_email_total} * 1 +
{Z-score of clic_email_total} * 2 / 20

(example) Z-score of page_view =

(sumOver(sum({page_view}),[company]) - avgOver(sum({page_view}),[])) / stdevOver(sum({page_view}),[])

percentile_5 = percentileOver(({raw_score}),5,[])
percentile_95 = percentileOver(({raw_score}),95,[])

standard_score =

ifelse(
    1 + (
        ({raw_score} - {percentile_5}) * 4
    ) / ({percentile_95} - {percentile_5}) < 1,
    1,
    ifelse(
        1 + (
            ({raw_score} - {percentile_5}) * 4
        ) / ({percentile_95} - {percentile_5}) > 5,
        5,
        1 + (
            ({raw_score} - {percentile_5}) * 4
        ) / ({percentile_95} - {percentile_5})
    )
)

The problem is that as output I have an aggregated table per company with the score column that disappears when I filter by subtopic. This is the starting database:

mail xxxxx@gmail.com
company xxxxx
activity details activity
fase_scoring_companyup awareness
subtopic_companyup cloud,telco,sicurezza
page_view 0
submission 1
open_total_email 0
clic_total_email 0

How can I maintain a score similar to mine but that is always visible? Note that the output table I need must be aggregated by company.

2

Hello @andreab !

What do you mean by your score column disappearing when you use a filter on another field? Is the returned value Null or 0, and does this happen no matter what value you filter your subtopic field to or just a specific value?

3

Hi @duncan

When I filter by the subtopic column, the returned value is null. It seems that the subtopic field is not considered in the score calculation. However, I can’t add it in the aggregated calculated field because a company can have multiple subtopic rows. Essentially, I would need to add a pre_filter in the calculation, but I can’t, as all fields are aggregated calculated fields.

4

Hi @andreab,
It’s been awhile since last communication took place on this thread, are you still encountering the same issue or were you able to find a work around?

If still in need of further assistance, it may be easier to understand your case if you could create a copy of your analysis with anonymized data in the QuickSight Arena View so that we could test out potential solutions.

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

5

Hi @andreab,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!