Aggregate count function does not work in the denominator

AMousavi
AMousavi Member
in Charting

When I use the following statement in a card summary, it works properly. But when use it as a value in a Single Value card, the count function doesn't work anymore:


SUM(CASE 

WHEN ROUND(100*`Score`/`PossibleScore`)>=60 THEN 1

ELSE 0

END)

/

COUNT( `AttemptNumber`)


For more context, the Attemptnumber column has 450 rows where each row contains a number same number (e.g. 1 or 2, etc). Therefore, I'm expecting to count 450 of them and get 450 in the denominator. But it ends up with the value of only one of the samples (e.g. 1 or 2, etc). I hope I explained it clearly.

Oddly enough, when I changed the division operation to addition it works! Any suggestion and comments are highly appreciated.

Thanks,

Musetti

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @Musetti Do you have any sorting or grouping in your Single Value card? If you do, try removing it and see if that fixes the issue.

Answers

  • RobSomers
    RobSomers Contributor

    @Musetti, Since you're looking to make a percentage, try removing the 100* from your beast mode and then change the format of your value to be a percentage.

    image.png


    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • AMousavi
    AMousavi Member

    @MichelleH you just solved the problem! I removed all of the sorting and now it gives the right number. Amazing!

    @RobSomers that is actually great idea. Thanks!

    Musetti

  • yentran
    yentran
    edited November 2022

    Hi all,

    I have issue with using aggregation formula in Single Card Value.

    • When I create a Beast Mode metric with rule "SUM(`OVERALL_SCORE`+0) over (partition by `VISIT_ID`)" => Drag into Single Value card => It displays 770
    • When I create a Beast Mode metric with rule "COUNT(DISTINCT `VISIT_ID`)" => Drag into Single Value card => It displays 8

    However, when I create a Beast Mode metric with following rule and drag to Single Value Card

    SUM(`OVERALL_SCORE`+0) over (partition by `VISIT_ID`)

    /

    COUNT(DISTINCT `VISIT_ID`)

    => result is 323.33 which is incorrect. It should be 96.25 (770/8).


    Could anyone help me with this issue? Thanks a lot!!!

Categories