Formulas - anyone having challenges?

[hlorosc]

Hi y'all!

Sisense newbie and hoping to get some help on formulas. In our trial, I was able to build formulas for activation rate (as an example), where I counted the number of customer IDs (a string field) with a filter by activated and divide it by the count of total customer IDs. 

So essentially, the formula was: 

COUNT([customer string ID], [activation indicator filtered to yes])/COUNT([customer string ID])

It worked great! I was able to crank out a bunch of "rates" (activation, conversion, etc) in no time - and it was way easier than doing SQL queries as we're doing live models so can't calculate custom columns in the data set (correct?)

But now that our hosted instance is live... the formulas don't work at all. I saw the documentation is the same, but it now requires a numeric field (which wasn't an issue in the trial). So I changed to a numeric - no dice.

I saw that there may have been a change in how "filtering" with aggregation worked, so I tried COUNT([activation indicator filtered to yes],[numeric ID])/COUNT([numeric ID]). It let me save, but then threw up an error saying there was an issue with my live model. Every. Time.

I'm hoping maybe someone here is:

1) Using live models (we're connected to BQ)

2) Using formulas with "conditions" like I am to calculate rates

3) Having luck getting them to work! Would love to model off your success!

I have to migrate some critical business dashboards in the next week and a half, and this is a huge issue. I've contacted support as well but figured coming to the community is always a good idea as well (and I can learn!) 

[irismaessen · Accepted solution]

Not using live models, but in my few experiments with them I haven't noticed a difference in how the formulas work. I use filtering in formulas extensively.

I would try the formula like this: (COUNT([CustomerID]),[activation filtered by 'yes'])/COUNT([CustomerID])  -- so using slightly different placement of parentheses.

If for whatever reason you are also applying a grouping on CustomerID itself, you can try:

(COUNT([CustomerID]),[activation filtered by 'yes'])/COUNT(ALL([CustomerID]))

Some of the aggregation functions (SUM, MAX etc) allow two parameters to the function, where the first one is a group by and the second another aggregation. But COUNT generally only takes one. And the filter is then applied outside the aggregation.

Good luck!

[szimmermann]

I work with a live model and have run into this same type of issue a couple of times now, filtering a calc on a different field/flag-type value. Just so I'm understanding the issue, are you saying the calcs fail in a widget/dashboard or are you working queries in the live model? I'm assuming [activation indicator filtered to yes] is a dimensional field/column in your table and it is Boolean.

If so, sum([activation indicator filtered to yes])/count([numeric id]) might achieve the result you're looking for. Let us know how it works out!

[hlorosc]

@szimmermann - it's failing in a widget/dashboard. Either the formula builder (fx) throws an error when I'm trying to build it (and I can't save) OR I can save but then get an error in my widget that there's an issue with the live model. 

I'll definitely update here with solutions and so appreciate the help thus far!

[hlorosc]

@irismaessen - the extra parens did it - much appreciated! helps to have another set of eyes when you're coding all day 🙂