Hello everyone. I'm struggling because I want to create buckets or bins, to show counts of Donations received based on the Amount donated. The ranges that the managers want to see are from: "$5 - $99", "$100 - $499", "$500 - $999", "$1000 - $9999", "$10000 - $99999" and "100K+".
I've been trying to create new rows for each of these ranges in the data cube with a formula to return a 1 if Amount >=5 and <=99, etc, but it could be a syntax error and it's not working. How can I do this?
Share picture of where you have this formula please, with the script you have written for it.
I tried doing it as a Calculated Element in the Data Cube. See the formula below as an example in the script editor, for the $100 to $499 bucket I want to create. If there's any other suggested way, it's welcome too.
You need $Amount$ again.
if($Amount$ >= 100 && $Amount$ < =499)
return 1
Also, check what data type you have set it to.
Hi Gerardo,
If you’d like to calculate the count of “Donated Amounts” in each ranges of "$5 - $99", "$100 - $499" and etc., you may create a Calculated Element at cube level to show the ranges (e.g. $5 - $99) or range symbol (e.g. 1, 2 or etc.) for each row and then use Hierarchy Count Measure or Count Measure at metric set level :
Here is a sample script to create a “String” type calculated element to show the range of Amount donated. You may customize the following script and use it as the Expression of the Calculated element:
//Get Amounts donated
int tmpAmount = $DonatedAmount$;
//Decide what to display as the range
if (tmpAmount >1 && tmpAmount <=99) {
return "$5 - $99"; }
else if (tmpAmount <500 && tmpAmount >99){
return "$100 - $499"; }
else return "$500+";
Submit your changes and open “Data Preview” panel, scroll to the right, the new column should be displayed showing the ranges. Now, you can use Hierarchy Count Measure on your metric set to show the counts of donation received (in each range).
However, if you’d like to show numbers instead of string ranges, you may select the data type of the Calculated Element as “Int16” and return numbers in your script (e.g. return 1;) and finally use Count Measure on your metric set.
Thank you all! I worked it out!!!
