Explore the Community

Hello,I would like to create a metric that count the number of deals per month depending on the dimension below. In order to do so we should use “ ID prm client and “date of first contract signature” from the below dimension.How can i do that ?Thanks for your help.

Dear Community, I have a transaction List and have a Property which Type is Dimension (Month) Text.I’m looking to create a metric that tells me the latest month on that property. How can I achieve that? Thanks in advance :D

Dear community, This is a quick one. I have a Boolean metric with one dimension (Employee). The metric shows a list of employees with the tick box marked.I am struggling to generate a KPI that show me the number of employees in the list. What’s the best way to approach this outcome?Many thanks in advance :D Metric: if(Transaction List. Bolean property'[by firstnonblank: Same Transaction List.Employee Name],true) I obtain a list like 

For all the new joiners, a quick reminder of what you get with this newsletter:👉🏾 Around 10 fresh job offers👉🏻 From top companies worldwide👉 Every weekExecutive Positions  FP&A Director [IT SERVICES & CONSULTING] 📍 San Francisco, US Visa is a world leader in digital payments, facilitating payment transactions between consumers, merchants, financial institutions, and government entities across more than 200 countries and territories each year.   Head of Strategic Finance [EDUCATION] 📍 Vienna, AT GoStudent is one of the world’s leading tutoring providers and, with a €3bn valuation, Europe’s highest-valued EdTech company.   Head of Corporate & Strategic FP&A  [ENERGY] 📍 The Hague, NL Yinson Holdings Berhad is a global energy infrastructure and technology company, listed on the Main Market of Bursa Malaysia Securities Berhad.   Hiring? Submit a job! Other Positions  Strategic Finance Analyst [SOFTWARE]  📍 London, U

I tried to calculate the average MRR invoiced per cohort but it’s not working.i tried to drill down but dit not find the solution.For example: MRR by cohort for Cohort 14 in jan 23 is equal to 4315€ and '# MRR invoiced by client is equal to 6.The calculation should do 4315 divided 6, but it’s not.Can you help me ?Thanks  

I want to create a Metric for Customer Count. We have ACV $ and Churn $s, do you know you and/or someone on the Support team can help / work together to make sure I am doing it right? Can we jump on a quick 30min call? TY

Hello!I am trying to build an org chart for my company. Most part is good to go, but I noticed that the “Position <> Manager” metric for Reporting seems to confuse the overall structure at the end. When looking at below snapshot, I clicked the ‘Strategic Finance’ team and employee A” is our CFO. My goal is to show “B” and “C” employees underneath “A” employee only because this flow is for the “Strategic Finance” department, However, it’s showing +6 who are NOT part of “Strategic Finance” department. Is there any way to fix this? Thanks!!   

Hello,I have used the LQ calculated item to create a table but the amount displayed in April 2023 LQ is the amount of jan 23 actual.  Can you help me ? Thanks  

When building & refreshing an existing connection to Pigment, the view I have saved does not pull through to google sheets in the format that I saved the view. It also does not pull through all the page filters that I have, specifically the years page filter.This causes the whole import to pull through for all existing years for all scenarios that I am pulling in. It also pulls through all data rows even if there is no underlying data. For example I am pulling a full P&L and instead of pulling through just the Revenue Accounts for Account L1 Net Revenue it also pulls through every Opex, CoR, etc. account L0. This causes a ton of extra useless empty rows in our google sheet. This is a lot to scroll past & causes noise in our workbooks. Is there a better workaround here? Right now I have one tab just for the Pigment connection & then have to manipulate the data on another tab to get the format cleaned up. 

Hello community, I’m creating a transaction list to be inputted manually by an end user.How can I have a reduce list for a certain dimension?eg: in a country dimension with all countries, the list available needs to be only the European countries. As a workaround I’m creating extra lists to have these sub-lists. But it will not be a very clean strategy in the end. Does anyone have another way to overcome this?Thank youSofia

I have a dimension that is a number (30 Day Cohort).  I think I will need an identity matrix for some calculations later in my model.  Is it possible to create a metric that has the 30 Day Cohort x 30 Day Cohort?  Here is a simple example of what it would theoretically look like.I have tried playing around with the Value function to convert to numbers and do some sort of comparison to the dimension but it doesn’t return what I would like:IF(VALUE('30 Day Cohort'.Name)<VALUE('30 Day Cohort'.Name),0,1)[BY SUM: '30 Day Cohort']Any ideas or things to try would be much appreciated.  At the end of the day, the 1s in the matrix will eventually be values calculated from other metrics but I think I need this matrix to do those calculations.  But then again I may not given Pigment’s various functions and how it handles dimensions.  So any and all ideas are welcomed :)  Thank you in advance!

I have a metric that has two dimensions that have pasted values from another source.  What I would like to do is return the last month that there is a value > 0 for a specific row in the metric.  In Excel terms, this would be done with a MAXIF function where I would return the max month with the condition that “Net Installs” is > 0.  From the screenshot below I want to return the month dimension value of Dec 24Can this be accomplished using just the BY modifier and the MAX aggregation method?  Any insights are very much appreciated.  Thank you in advance.   

For all the new joiners, a quick reminder of what you get with this newsletter:👉🏾 Around 10 fresh job offers👉🏻 From top companies worldwide👉 Every weekExecutive Positions  [SOFTWARE] FP&A Director,📍Remote, FR Cint is a global software leader in digital insights gathering and has the world’s largest consumer network for digital market research, made up of over 155 million engaged respondents across more than 130 countries.  [MANUFACTURING] FP&A Director,📍Franklin, US Mars is a unified force of 140,000+ Associates and the world’s leading manufacturer of chocolate, chewing gum, mints, and fruity confections.   [MANUFACTURING] FP&A Director,📍Boston, US By delivering ultra-high performance and energy efficiency, Lightmatter’s compute engine will power rapid advancements in AI while lowering its environmental footprint.  Hiring? Submit a job!Other Positions  [FINANCIAL SERVICES] Strategic Finance Lead,📍London, UK

Hello, i would like to do the sum or the average of last 12 months before the switchover month. Thanks for your help

Hey team, I have an issue to chose the proper modifiers in one of the metric blocks. I have a quota values by month by sales rep and wanted to sum these values into the quarters as a first step. So I’m writing a formula= Metric block with quotas by month [by sum: Month.Quarter]Next step is to use in quarter seasonality which is by Month, see the screenshot below. Total for a quarter is 100%. What’s the syntax to spread quarterly quota value to months ( I was testing add constant but it didn’t work) and then use the in quarter seasonality?  

Hello,  Can you please help me creating a formula to show net working days for each month?The excel formula will be like this: So far, I created a dimension  I referenced the article here but could not really understand what I should do to get list of working days for each month.  Thank you!

Hi,What’s the criteria to have a another level of breakdown?I was able to have two levels of break down for one of the metric but for the boxed one “R&D SAAS Total” another level of break down doesn’t show up even though the  dimensions are all different for each level that I wanted to select. Can anyone share what is the rule/condition to be followed for such multiple level of break down in a table?Thank you! 

Hello, can you please help me with this formula ?I would like to do a formula similar to vlookup between Unique item values (text format) of two transaction lists (check if a transaction list contains (or not) the Unique item values of the other transaction list → if Unique ID of TL1 is in TL2 then yes, otherwise no) Is it possible to use vlookup equivalent between text format values, or do I have to create a dimension ? Thanks in advance!

For all the new joiners, a quick reminder of what you get with this newsletter:👉🏾 Around 10 fresh job offers👉🏻 From top companies worldwide👉 Every weekExecutive Positions  [MANUFACTURING] VP of FP&A ,📍Atlanta, US "This role will also provide strategic financial analysis, recommendations and guidance to the Executive Leadership Team on a wide variety of business topics and initiatives. This is a critical position on the Dematic Finance Leadership Team and will lead a group of talented executives spanning all areas of FP&A and Finance Business Partnership."  [SOFTWARE] Global VP of FP&A , 📍San Francisco, US "This is a great opportunity to lead and be a part of a high-impact team in a high-growth, publicly traded, subscription software company approaching $2B in revenue. We provide analysis to support strategic decisions, conduct financial reviews of various business areas and manage financial processes such as planning, forecasting, reporting

Hello here!I hope that you are well.Please, is there a way to evaluate the size of an application (like Mb for excel) or the number of calculations made in a Metrics? Sometimes, the calculations take time and I would like to know from where I can remove complexity/heaviness. Many thanks

Hi Team I need to compute cell by cell XIRR . So for example, first IRR will be calculated from FY21 to FY 22, FY21 to FY 23 . them FY21 to FY24 and goes on and on. Is there a way to achieve this?  So its an expanding calc starting with FY21 and keeps on expanding by brining in additional year. ThanksMusab 

For all the new joiners, a quick reminder of what you get with this newsletter:👉🏾 Around 10 fresh job offers👉🏻 From top companies worldwide👉 Every weekExecutive Positions  [RETAIL] FP&A Director,📍Plano, US "This critical position will have responsibility for the development and implementation of an enterprise level long-term value creation model, maintain and drive adherence to a corporate financial planning calendar, and provide advanced analytical and strategic support for internal and BoD meetings and communications."  [SOFTWARE] Head of FP&A ,📍Luxembourg, LU "At Amazon, we're working to be the most customer-centric company on earth. Operations is at the heart of what we do, delivering hundreds of thousands of items each day and fulfilling customer orders from all over the world. The Operations team is seeking a Head of FP&A to guide financial analysis, planning and control for a region in our fast growing Rest-of-the-World Operations Ne

Hey Team, I have a transaction list with all employees in the Workforce planning and in the same application we created the Employee ID with various properties mapped to this dimension like country, department etc. I would say classic Employee ID dimension block with a mapping. Now we have a need to add additional dimension property at the Employee Id level for a respective department which can be added manually employee by employee by the other team who makes the allocation of sales forces in the other application i.e. it’s not a Workforce app. What type of block we should create to have a kind of a table where we have a list of employees and then column with the list of properties for the allocation?PS. We don’t want to do it at the transaction list level due to security and also this infotramtion will never be available in our HR system to import it. Thanks! 

Hi Pigment team!  Is there a formula to look for the names dimension name in the Memo, and populate the EE dimension in the new property? Thanks in advance!!   

We’re pleased to announce that Pigment has been recognized in the 2023 Gartner® Cool Vendors™ in Capital Investment Management Software report.   According to Gartner:“FP&A software tools often require customization to meet the life cycle of capabilities for capital investments, creating a market opportunity for emerging players. This research introduces FP&A leaders to emerging solutions with differentiated capabilities to digitalize capital investments.” Cool Vendors in Capital Investment Management Software report, 10 August 2023, Michelle Carlsen and Vaughan Archer. Read the full report here! DisclaimerGartner is a registered trademark and service mark and Cool Vendors is a registered trademark of Gartner, Inc. and/or its affiliates in the U.S. and internationally and are used herein with permission. All rights reserved. The GARTNER COOL VENDOR badge is a trademark and service mark of Gartner, Inc., and/or its affiliates, and is used herein with permission. All rights reser