Explore the Community

Hi,I’m trying to calculate a moving average where blanks are counted as zero’s. When trying to leverage the “is defined” formula I get a calculation time-out error. Please advise.

Hi Guys, I have a beginner’s question. I am not entirely sure I understand the difference between BY and ADD. I understand both can be used to split the data into additional dimensions but not sure when I should use which. For example - I have data by Period and Account. For the purpose of dimensions alignment with other metrics, I need to add Channel and Country assuming the same values for Channel and Country per Period and Account. I know I can use BY or BY CONSTANT but what if I use ADD? What would be the difference? Regards,Adam

Hi!I’m having issues with a formula. My formula is currently looking like this:The result I get is the error message Function error: function IF argument 2 and 3 need to have the same type. Any idea of what I need to do to get the dummy name in the CC# column?

Is there a way to apply conditional formatting to a column/row with the option of removing the formatting on specific cells within that column/row?In the attached example, I would like the bottom row to not be subject to the conditional formatting. 

Dear Pigment Community,I'm currently tackling a challenge with a modeling project and could really use your expertise.Goal: Forecast future customers using the average months to convert.Issue: I have a metric for my forecasted number of customers, but I'm stuck on how to shift these values forward into the future based on my metric that calculates average months to convert. Desired outcome: My ideal solution would take the forecasted customer values and shift them forward into the future by ‘x’ months based on what the average months to convert value is. Please find images and further explinations bellow.This metric contains the number of custstomers expected.

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 weekHighlights  [SOFTWARE] FP&A Analyst 📍München, DE

Hello,In my transactions list, I have a field called “Chain Simulate” which merges the dimensions “Ship Mode”, “Ship Mode Simulate” and “POL (code)” and returns as text property. Is there any way to merge the three dimensions and return in dimension property? The combinations are already created as a dimension in the background. My purpose is to : Based on the combination in the column “Chain Simulate”, it should return a number in the Lead Time column. Besides, the Lead time per combination is added within a dimension & also created a mapping Metric. Issues : I am able to merge & return the value only as a “

Hello,We import our sick leave period data into a transaction list in Pigment:Each employee could have several records of different periods.But it's possible to have overlap periods for one person, for exemple: Emp_2 has an overlap period from 03/04/2023 - 03/08/2023. So for this case, I have two needs:1. Create a metric to affiche those employees who have overlap period2. Even it's error and we warn it, we still want to calculate these sick leave period from the earliest start date to the lastest end date. 

Hello Pigment Community. I am trying to calculate the commission that we have to pay to our partners and it is based on the first year of MRR that each new client signed through a lead from our partners has generated. That means, that if we sign a new client in 2022, on 2024 we will pay a commission for that client based on the MRR that it generated through its first year, in this case 2022 and not the MRR that it is generating now in 2024.  At the moment I have this formula; This is showing me only the first 12 month of MRR for each client as you can see in the following screenshot:  The problem with this method is that if we have a client that starts i

Hello Pigment!I am having an issue with a formula.I have a dimension called 'MRR-D Invoice '. I want to create the column “latest_suscription_duration”  which takes the same value as“subscription_duration” according to the max end date, and that, for each customer. This is similar to a group by function in sql. Anyone knows how to do that ?   I tried this but it gave me weird results because I didn’t group by customer reference :'MRR-D Invoice .'Subscription_duration'[BY max 'MRR-D Invoice '.'End_date' ] PS : End_date is a dimension, Subscription_duration is an integerCustomer_reference End_date  Subscription_duration 

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 Roles  [IT SERVICES & CONSULTING] FP&A Senior Director / 

Hi,I currently have two metrics and three dimensions involved (Job position, Employee, Month):Metric Manager_job position, with two dimensions Month and Employee, data type is Job position, where I store the manager’s job position for each employee by monthMetric Employee_job position, with two dimensions Month and Employee, data type is Job position, where I store their own job position for each employee by monthI tried to create a metric with Month and Employee dimension, data type Employee, which shows me the Manager’s Name (dimension Employee) for each employee by month, but I didn’t success to make it. Can you give me some advice?Thanks in advance!

The below pulls through monthly costs from an input. 'Cloud Cost Input From Marketing'[by constant: 'Cloud Cost From Marketing Team'."Staging"]I would like to pull through only the latest available month but have tried lastnonblank and either im doing it wrong or its not working. Any help would be appreciated,  

I am trying to show just the quarter end and year end headcount. However, rather than taking the last month in the quarter or the year, the quarter end numbers sums every month. How do I set up headcount so the quarter does not show a sum of the periods but rather the last period? For now, I’ve created a report with just the last months of the quarter but I plan to add headcount to the bottom of a PnL which has accounts summed in the quarter.

Hi Pigmenteers! When reading Top Tips for modeling in Pigment - Part 2: Performance Optimisation, there’s an idea as highlighted in the screenshot below: to avoid doing too much within one metric, there’s “a good guideline is that, if you have to copy and paste part of a formula more than once, it may be worth its own metric” - I don’t quite get it, can you help explain this idea to me?  

Hello,I need to create a form for end users to assign  a cross-dimension (direction and activity) to 2 new dimensions (Analytical Dim level 1 and Analytical Dim level 2). To do this, I was thinking of:Creating a table with the direction/activity cross and the associated data. Creating 2 metrics with the direction and activity dimensions A. to select: Analytical Dim level 1 B. to select: Analytical Dim level 2However, I would like the selection of "Analytical Dim level 1" to only display the child items of Analytical Dim level 2. Is it possible to do this and if so, how?Thank you in advance for your help.

Could someone please help let me know if I could have my calculation shown in points vs bps? Thank you

Hi everyone, I am trying to display images in my reports but i get this error message “The image couldn’t be displayed” The link is working when pasting it in the browser, and the source is authorized Here is the link of the Image i would like to display : https://drive.usercontent.google.com/download?id=1P7ssJsoxLzsQjkGdRf_b4EFTYvIbAQmr&authuser=0 Thank you in advance for your help

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 Roles [SOFTWARE] FP&A Director 📍Remote, US Gong

Hi, I’m trying to add a view of a monthly total headcount metric (a cumulative value, dimensioned by month) to quarterly and annual tables that will show only the final headcount from the last month of each period. For example, I know Pigment’s default behavior is to sum this cumulative headcount across every month of a year if I add the headcount metric to an annual table directly, but I need to isolate December 31 headcount only. Is there a relatively simple way to create these period-end summary numbers?Thanks!

Hey there,I’m trying to use yesterday’s revenue to forecast the rest of the month’s revenue.For that, we created a Day dimension, which is a calendar type.Strangely, when displayed in the dimension itself, it looks normal (like a date).However, when we add it to a metric pivot it always adds the time. How do we stop it from doing this?I’ve already checked the Day dimension Special Properties and Default Display Name and it just uses the Name column for this.Images below.Any help is much appreciated! Day dimensionDay dimension used in a metric pivot 

Hello,I am trying to calculate the number of employees who left by hire date cohort and number of months that they stayed in the society. Here is the formula I have (but doesn’t work because it is too complicated I think):Employee [BY COUNT: Employee.'Cohort Month', Employee.'Month tenure cohort', Employee.Country, Employee.'First Job Position'.'Team Graph HR']The Cohort month corresponds to the month of arrival.The Month tenure cohort corresponds to the number of months the employee stayed in the society.I want this metric by team department (Team Graph HR) of their first job position (because some employees can evolve from one job to another in the company).Could you please help me finding the best formula here?Thanks!Elodie

Curious to know if there any 1st or 3rd party connectors to get capiq data directly in pigment automatically.It’s not too difficult to upload a CSV on a monthly cadence, but would be great if there was a live connection. The alternative is to send this to an internal DWH then to Pigment, but that might be a lot of internal work from our BI teams.

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 Roles [RESEARCH SERVICES] FP&A Director 📍Wa

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 Roles [STAFFING & RECRUITING]