Using Histogram formula with avg

#1

Hey, all. I’m trying to have DBI use a histogram formula to arrive at some bins in my metric set, but not having much luck. Here’s a screenshot: https://www.screencast.com/t/rzRSY0mzJ4d

You can see my formula: HISTOGRAM($RESPONSE$,50,0) where $RESPONSE$ refers to the AVG patients treated measure, but nothing seems to appear in the far right column. I’m trying to do bins of 50.

Am I using the wrong syntax? Wrong formula? Both?

Thanks for any advice!
-Ryan

#2

Hi Ryan,

By design, the HISTOGRAM formula can only be used for formula visualization. You can check out the following documentation providing more information about HISTOGRAM formulas here:
https://www.dundas.com/Support/learning/documentation/design-view/formulas/statistical/histogram

In order to create distribution bins column, I would recommend you to calculate them in the data itself or you can use our calculated element transform in the data cube to return distribution bins as required by using JavaScript. Check out the sample script below:
if($Sales Amount$ >= 40 && $Sales Amount$ <=60){
return “>=40 / <=60”;
}
else if($Sales Amount$ >=60 && $Sales Amount$<=80){
return “>=60 / <=80”;
}

You can check out more about calculated element transform here:
https://www.dundas.com/Support/learning/documentation/cleanse-consolidate-modify-data/transforms/common/calculated-element

1 Like
#3

Thanks, Abhay! Can I use that script you wrote in a formula? I tried it via “Add a Formula” to my metric set, but it’s giving me a syntax error: https://www.screencast.com/t/Zs8QWY1T

Or is there some other way to do this? I was thinking some kind of IF/ELSEIF formula was what I needed, so really thought we were on the right track here!

Thanks,
Ryan

#4

Ryan,

The reason why we cannot use script in the formula is because in the data visualization, we can add formula under measures only which expects a numeric value to be returned. By using this script we are trying to return a string value which is causing the error.

The best way to generate string data in the data set and add it into the data visualization will be using Calculated element transform only as explained in my previous comment. If you find any troubles setting this up I would encourage you to reach out to me at support@dundas.com and we can have a web meeting where I can answer all your queries regarding the use case.

1 Like
#5

Thanks, Abhay. I meant to update this thread yesterday, but I did indeed go into the data cube and built calculated elements there. I just wrote some IF/ELSE statements as you suggested and returned either a 1 or a 0 and then I could do sums, percentages, etc. in my metric set using those. Thank you so much for your help!

-Ryan