I have a lot of expressions in one expression and I would like to reduce the amount of code. The reason is that I am making a kpi dashboard.
Suppose: my KPI is: count the new projects (and show it per risk category). In short 1 have one expression which is evaluated in 3 expressions: Risk high, medium and low. So the only difference is the Risk = High is in the High expression and risk = medium in the medium expression. (per KPI)
The reference manual is correct. It also states not to use set analysis over fields in different tables. my advice to u wud be to make a single table for the fields and then apply. also double check ur formula for syntax errors.
But that is really a shortcoming of set analysis right? Because an If statement work great at all times, and with set analysis you might get wrong results.
Because I am working with tables like, engagements, hours, assessments which are all related to each other in most of my queries. I can't join them because then the amounts will be incorrect.... So I have to keep separate tables.
mmm, I think I have to create 1 big table then, create flags etc.... Or use the slow if statements....
The manual warns against using the set operators with multiple tables, not set analysis per se. The set operators are the union +, exclusion -, intersection * and xor / operators. I have not actually experienced any problems with them myself.
I think you can use set analysis quite freely across multiple tables, assuming that there is some association between the tables. On large data sets, the use of if statements in expressions can cause serious performance issues while set analysis performs far better.
Sometimes it is necessary to modify your data model as your needs change, but I would try set analysis first.
For your original post, have you tried removing the risk grade field dimension from your aggregate? Also, if you post your app or a sample, it may help to understand the problem.
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein