Formulas against specific value

#1

I am fairly certain what I want to do here isn't meant to be done in the front end like this, but I figured I'd ask here and see if I'm missing something.


This is in the Insurance domain, working with what are called Loss Triangles.

Loss Incurred Triangle:

Image titleLoss Development Triangle:

Image titleCurrently, these point at two seperate sources of data. One thing I'd like to be able to do, is take the first triangle, and calculate the second triangle from it directly on the dashboard. However, that isn't the purpose of this post. What I really want to do from this point, is produce another table as follows:

Take the LAST value of each Policy Year and Multiply it by the Average value for each Loss Development factor down to the development period (in the denominator) of that last value.


More explicitly in this particular case:

2013: Last Value 1,113,870 ; Multiplied by: Nothing (there are no value with 4 or higher in the denominator)

2014: Last Value 1,261,805 ; Multiplied by: 0.9999

2015: Last Value 2,067,102 ; Multiplied by: 0.9999 * 1.0202

2016: Last Value 7,294,836 ; Multiplied by: 0.9999 * 1.0202 * 1.0191

2017: Last Value 1,675,766 ; Muliplied by: 0.9999 * 1.0202 * 1.0191 * 4.3856


Is a calculated visualization like this even possible?

#2

Such calculation may be possible with a combination of formula visualizations but I suspect it won't be that straight forward and will require a chain of those formula visualizations that can complicate things. The LAST formula will give you the Policy Year last value but then to multiply it by the Average value for each Loss Development factor down to the development period will be tricky, especially if the number of policy years/periods is expected to grow over time. Instead of trying to chain many formula visualization together, I would recommend doing those data calculations at the data cube layer using joins and calculated elements. Alternatively, if you really prefer to do the calculation at the dashboard layer, you can create a new formula using the API that will execute this logic for you.



#3

In the worst case - and I have done this before(!) - you can have both tables off canvas and run a script on data changed that creates, positions and populates, or just populates data labels arranged in a grid formation!