Formulas - anyone having challenges?
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!)
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!
