Subtracting a "COUNT" result from a given value

ChrisGaffan

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.

MichelleH

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

GrantSmith

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`) 

ChrisGaffan

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

MichelleH

@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

@MichelleH

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

Returns an even larger value.

MichelleH

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

ChrisGaffan

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

MichelleH

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

GrantSmith

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?

ChrisGaffan

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

Thank You both.