How to use a beast mode calculation in a formula

Reply
Yellow Belt

How to use a beast mode calculation in a formula

I am trying to use a beastmode calculation within another beastmode calculation. Is this possible? I keep on getting an invalid formula error

Formula 1 name: Filtered_data

Formula:

Case
When (COUNT (`Reading Temperature`))> 431 Then 1
else 0
End

 

Formula 2 name Valid_dataset

sum(Case
When (COUNT (`Reading Temperature`))> 431 Then 1
else 0
End)

 

Tried also 

sum('Case
When (COUNT (`Reading Temperature`))> 431 Then 1
else 0
End')

 

and

sum('=Valid_dataset')

 

All gave me an Invalid Formula error

Yellow Belt

Re: How to use a beast mode calculation in a formula

If you want to use a beastmode calculation inside another beastmode field, don't reference it by name, instead double click the field in the 'DATASET FIELDS' section of the beastmode window. It will paste the formula used in the first calculation into the current beastmode calculation (prevents things from breaking if you delete the first beastmode field later).

 

Your formulas are close, try this:
SUM(CASE WHEN(`Reading Temperature`> 431) THEN 1 ELSE 0 END)

Moderator

Re: How to use a beast mode calculation in a formula

@vcsmedia, did Devinr's reply help you out? 

Yellow Belt

Re: How to use a beast mode calculation in a formula

Thank you Devinr but that didn't work, May be I have to explain it a bit differently since its a bit confusion to picture. 

 

The first formula is looking at the total count of all the readings and if the count is greater than 431 it gets a value of 1

 

the second formula needs to count how many 1's I have. 

 

The suggested formula is looking at each reading individually and since none of the readings will be greater that 431 then they all get a value of 0 and my sum is 0. 

 

 

Yellow Belt

Re: How to use a beast mode calculation in a formula

Vscmedia,

 

I'm still not entirely sure how your data is setup, so let's start there.

 

Is your data something like this?

Location | Reading Temperature | Date
SpotA,78,1/1/2015

SpotA,77,1/2/2015
SpotA,79,1/3/2015
SpotB,88,1/2/2015
SpotC,77,1/1/2015
SpotC,79,1/2/2015
SpotC,80,1/3/2015

 

1) And you are trying to find how many `Reading Temperature` entries there are for each location?

2) Or are there no 'Locations' and you just want to find the total count of rows with an entry in the `Reading Temperature` column?

3) Or am I totally off?

 

If it's either of the first two, it seems like you're trying to make a beastmode column where you really want a totals row. You can either make a Sumo Table (like an Excel pivot-table) to show the total count or make a dataflow to perform the calculations on your dataset. Dataflows are nice because it gives you pretty much full MySQL capabilities.

Yellow Belt

Re: How to use a beast mode calculation in a formula

Should have started there first, but you have the general idea.  The data is setup as follows:

 

 

 

        Shipment |Sensor#  | Route      | # of readings  |DataSet 

 

ex.  HB12345 | 34578      | ABC-XYZ    |300            | HB12345-34578

 

Each dataset has multiple temp readings, We have a formula that counts the # of readings <Count (Temp readings)> I am trying to show/count only those datasets that have more than 242 readings. Note that the DataSet field is also a formula field which concates the Shipment and Sensor#.

 

 

 

Yellow Belt

Re: How to use a beast mode calculation in a formula

Okay, so Beastmode won't help you in this case because you are trying to sum a group-by set of values. You can run your dataset through a MySQL dataflow to group/count/sum your data and use the newly created dataset to create your cards.

 

If you want to be able to see the underlying data, you can create a drill path that loads the original dataset as long as you keep your column names consistent.

Moderator

Re: How to use a beast mode calculation in a formula

@vcsmedia, did any of Devinr's replies help you out?

Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!