I currently have a YOY (Year-over-Year) insight analysis that generates two separate visuals:
Current visual view:
Top Movers - showing increases (e.g., “Post Audit Claims increased by 593.8% ($17.98M) from $3.02M to $21.01M”) Bottom Movers - showing decreases (e.g., “Provision for Account Receivables decreased by 34.77% (-$727.85M)”)
What I need: Combine both top and bottom movers into one single visual/ show all trends irrelevant of top or bottom Sort by percentage change (increase/decrease) at the related org level Display in a user-friendly narrative format like: “Post Audit Claims increased by 593.8% ($17.98M) from $3.02M to $21.01M”
Specific formatting requirements: Currency formatting: Automatically format large numbers to the closest unit (B, M, K, etc.) Currency symbols: Dynamically add appropriate currency symbols/codes based on organization: US org: Dollar symbol ($/ USD) Mexico org: MXN currency code Other orgs: Respective currency codes
Current setup: Data includes various holdback types (Post Audit Claims, Provision For Aged Receivables, etc.) Analysis is at invoice amount level by org (US, CA, MX, etc.) Currently using separate top/bottom mover computations
Questions: How can I create a single visual that combines both positive and negative movers and showcases a comprehensive commentary? What’s the best approach to implement dynamic currency formatting based on org? Can you suggest the most efficient calculation method to sort all movers (both increases and decreases) in one view?
Any guidance on best practices for this type of combined analysis would be greatly appreciated!
Hi @mohisaq , it’s easy to do using SQL but I am not 100% sure that it will work and look fine when you use SPICE. In short, you can try using a table with a single column that contains text like in the insights. You can use calculate fields with Table Calculations (window functions) to: get moves, rank them (by absolute vale), filter top X, generate the text for the table.
Following up here as it’s been awhile since last communication took place on this thread; did the solution provided above assist with your case or did you have any additional questions?
If we do not hear back within the next 3 business days, I’ll mark the solution.
Thanks Brett, your response was extremely helpful.
I was able to implement your suggestion of creating additional calculated fields but not for categories (My categories were already mentioned in form of text in my data). I used calculated fields to differentiate the values for each category. Since I only had 5 different categories to work with, I created 10 calculated fields (such as PFAR MOM, PFAR YOY, PAR MOM, PAR YOY, etc.).
After creating these fields, I used the total aggregation function within the key insights visual to display my content as shown in the screenshot.
I discovered there are essentially two approaches to achieve this through the insights visual itself. The first and easiest method, given that I only had 5 different categories in my fields, was to create calculated fields using ifelse formulas that displayed values for their assigned category only. I then used these fields in the insight visual with total aggregation computation.
There was also a second approach where I could combine the top and bottom movers computation within a single visual with some minor adjustments, as shown below.
