Subtracting a "COUNT" result from a given value

ChrisGaffan
ChrisGaffan Member
in Beast Mode

I'm assuming I have this completely wrong.

1344 - COUNT(`Room/Row Id`) is what i'm trying to do. 1344 represents the total spaces available in a cooler unit. the COUNT equation is how many spaces are currently filled.

The underlying math here is 1344-764=580, however i'm getting a result of 12,676.

Best Answer

Answers

  • GrantSmith
    GrantSmith Coach

    COUNT(`Room/Row Id`)  gives you the number of rows with that column populated so if you have multiple rows with that same ID it will count all of them. If you want to count the IDs just one time you'll need to do a COUNT(DISTINCT `Room/Row Id`) 

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ChrisGaffan
    ChrisGaffan Member

    @GrantSmith COUNT(`Room/Row Id`) alone returns 764 which is accurate. It's when I add "1344 -" that it spits out 12,676.

  • MichelleH
    MichelleH Coach

    @user058901 It's likely inflating the value because it's adding 1344 to every row. Try using AVG(1344) so it does not get duplicated

  • ChrisGaffan
    ChrisGaffan Member

    @MichelleH

    AVG(1344)-COUNT(DISTINCTRoom/Row Id)

    Returns an even larger value.

  • MichelleH
    MichelleH Coach

    @ChrisGaffan How much bigger? Is it correct if you remove DISTINCT?

  • ChrisGaffan
    ChrisGaffan Member

    @MichelleH With Distinct, it returns 12,716. Without Distinct it returns the original 12,676

  • MichelleH
    MichelleH Coach
    Answer ✓

    @ChrisGaffan How are you presenting this field in your card? Sometimes sorting can interfere with how the cards react

  • GrantSmith
    GrantSmith Coach

    It appears that you're somehow multiplying your original number by 10 as your result of 12,676 is equal to 1344*10 - 764

    Are you doing any aggregation with your beast mode? Can you paste your exact beast mode you're using?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ChrisGaffan
    ChrisGaffan Member

    @MichelleH @GrantSmith The sorting is what was breaking it. I removed the dimension from Sorting and it now works properly.

    Thank You both.

Categories