Creating a waterfall using deduction amounts

Nicholas_Campbell
4 yrs ago
8replies
Answered

I'm not sure if this is possible but I'm hoping to create a type of waterfall chart where you have the Gross Amount as the start point and then a series of deduction types (COM, BKG etc) to show deduction amounts and then Net Amount as the End Point. I can see how it works with dates using demo video but not deduction types.

Is this possible?

Thanks

Nick

- "making the sophisticated simple"
- AviPerez
- 4 yrs ago
- Reported - view
are your "deduction types" measures or items in a hierarchy?
- Nicholas_Campbell
- 4 yrs ago
- Reported - view
Hi Avi

I've tried three ways so flexible in whatever approach could or would work as I have the data in SQL behind it.

The first script I wrote had a single "amount" measure which then had a dimension to identifier what type of amount i.e. gross premium, net premium or a brokerage, commission, tax, fee etc.. I have a sort measure too so that gross premium is first in waterfall and net premium is end point. I got closest with this script

I have a second script which had each deduction type and premium type as an individual measure however when putting in multiple measures the waterfall chart begins to trellis

The third script i had is having a type field for premium or deduction then a qualifier to say what type i.e. premium has gross and net qualifier and deductions have many qualifier agian like tax fee etc..
- "making the sophisticated simple"
- AviPerez
- 4 yrs ago
- Reported - view
The out of the box waterfall is designed for your first approach - where the difference between categories is driven by hierarchical elements based on a given measure. This is how most waterfall charts are built. Can you explain why this did not work for you?

Your second requires a different logical way of drawing the waterfall. You can get this working using the "measure waterfall" chart found in the marketplace of the the custom visual tool (under Formulate) - where we have a specific example of this type of capability.
- Nicholas_Campbell
- 4 yrs ago
- Reported - view
The image shown is using the first method so not sure why its not working. I've attached where I put the dimension and measures to get that. you can see from the grid the values I should be getting versus what the chart looks like
- Nicholas_Campbell
- 4 yrs ago
- Reported - view
Is it possible to get more information by what is meant by hierarchical elements? Apologies I am quite new to this still..

At moment the only way I have telling Pyramid GWP is start point and NWP is endpoint is through a sorting measure, is there another way to do this?
- "making the sophisticated simple"
  
  AviPerez
  
  4 yrs ago
  
  Reported - view
  Nicholas Campbell I suggest you setup a support call where they can work with you through the problem interactively.
- Nicholas_Campbell
- 4 yrs ago
- Reported - view
Hi Avi

Just had a call and they've said its a not a feature of the current Pyramid version and recommended I put it here.

I've attached an image of how excel produces the data vs how pyramid produced the data (with the assistance of the support team). I think the thing excel does is lets you define the total column which seems to be the main difference.

I did try the custom visual and used the measure waterfall and for a team member who only has a few deduction types which are static it works perfectly however I have 1000's of policies with all different deduction types which change regularly so I cant create a measure for each new version realistically, also it doesn't feature tool-tips and some of the axis values look odd.

Thanks

Nick
- "making the sophisticated simple"
- AviPerez
- 4 yrs ago
- Reported - view
I believe the new Waterfall capabilities in 2020.02 close out the challenges you had in this thread.