Explore the Community

Connect with other users, discuss how you're using Pigment, and get inspired

605 Topics

Dave LNewly Minted Pigmenteer

Google Sheet connector and date format

We are pulling data from a view in Pigment into Sheets via the Pigment Connector and have noticed that the date information that is pulled does not necessarily reflect correctly.The month in Pigment, for instance, shows “Jan 24”. However, when this data is pulled into Sheets via the connector, the actual underlying date comes over as 1/24/2023 (which would be January 2023, and not January 2024). Is there a known way to account for this disconnect in dates between the month in Pigment and the way that gets translated into Sheets? Below are a few screenshots to illustrate the point: January 2024 date in Pigment translates to January 2023 in Sheets via the connectorJanuary 2024 date in Pigment translates to January 2023 in Sheets via the connector

21 days ago

Jacques d. S.Newly Minted Pigmenteer

asked in Questions & Conversations

Pull a dimension based on a number metric

I everyone,Do you know if there is a way to pull a dimension based on a metric ?Here is what I would like to do :I have a dimension that has :An ID number column from 1 to 1000 (manually filled) A Country dimension column (that I want to fill with a formula) A Month dimension column (that I also want to fill with a formula) An Activity dimension column (that I also want to fill with a formula)On the other hand, I have a number metric with the tree dimensions Month, Country and Activity, with an input based on a formula that gives me numbers from 1 to 1000.So in my dimension I would like to affiliate the month and the country regarding the column number, matching with the number metric. I tried Match and Item functions but it is not working.Do you know if it is even possible with Pigment ? Thank you for your help !

22 days ago

Shane CrinesJust Arrived

asked in Questions & Conversations

Automatic feed for SOFR Forward Curve

I am looking to find a way to setup an automatic feed for 1-month SOFR future rates in order to better forecast variable interest rates. Does anyone have any experience with this or have any recommendations?

23 days ago

Thu MaiBudding Pigmenteer

asked in Questions & Conversations

Native Scenario

Hi Pigmenteers 🤗I have 2 questions related to Pigment’s native scenarios:1. What are the main benefits of using native scenarios instead of custom scenario list? 2. I also learn that once you enable native scenarios in Pigment, the native scenarios will be applied to all the metrics in the application, is there a way we could remove it out of some specific metrics?

24 days ago

Gali BaramNewly Minted Pigmenteer

asked in Questions & Conversations

Calculating MovingAverage() treating blanks as zero's using ISDEFINED() times out

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.

24 days ago

AdamOccasional Observer

asked in Questions & Conversations

ADD vs BY

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

24 days ago

PetterNewly Minted Pigmenteer

asked in Questions & Conversations

Error: Function error: function IF argument 2 and 3 need to have the same type

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?

28 days ago

Gali BaramNewly Minted Pigmenteer

asked in Questions & Conversations

Dates exported incorrectly through gsheet connector

I’m running into an issue with the dates getting mixed-up when I load data from Pigment to google sheets (see the formula bar in the screenshot, the date ). The issue is that the title which is a text field appears as the header, and gets translated to a date incorrectly. I tried switching the header in Pigment, but the start/end date fields aren’t listed as options. Did anyone else encounter this? Any suggested solutions?

28 days ago

PatMacBudding Pigmenteer

asked in Questions & Conversations

Conditional Formatting - How do you override specific cells with static formation?

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.

28 days ago

Thu MaiBudding Pigmenteer

asked in Questions & Conversations

Ability to add multiple metrics with same dimensions at the same time

Hi Pigmenteers 🤗I have an input table with 10 input metrics (same dimensions, serve the same purpose). I know how to create them 1 by 1 directly from the table (more convenient), but I’m curious whether there is a way in Pigment we can create them all at the same time? 🤔

29 days ago

dariousFrequent Reader

asked in Questions & Conversations

Forecast future customers using the average months to convert

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.I want to use this metric to push the values in # cust fcst forward into the future by the number of average months.Example using the images above:metric[# Customers FCST]: France, Inbound Nov 23 value will be shifted to Mar 24, 4 months into the future because the metric[Avg months to convert previous year] value is 4. I'm open t

29 days ago

BenjiEmployee

published in Job Board

The FP&A Jobs Report #75 (Adobe, Gong, Reddit, Ubisoft...)

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 Commercetools is the leading composable commerce platform, allowing companies to dynamically tailor and scale shopping experiences across markets. [MANUFACTURING] Head of FP&A 📍Dublin, IE Zoetis is a global animal health company that develops, manufactures, and markets veterinary medicines and vaccines for livestock and companion animals. [SOFTWARE] FP&A Director 📍Remote, US Gong is a revenue intelligence platform that leverages artificial intelligence to analyze sales interactions, providing insights to help improve sales effectiveness and drive revenue growth for businesses. [HOSPITALITY] Head of Strategic Finance 📍Luton, UK TUI is one of the largest leisure, travel, and tourism companies globally, offering

460

1 month ago

PaylwardNewly Minted Pigmenteer

posted in Questions & Conversations

Advanced Modeling Skills

Hello,I am working on a business case where I need to take the data and create some sophisticated business items. I want to see the forecast at different levels by who is inputting it and how to confirm the forecast is done. Here are some of the things that need to come out of the data that is provided.Alignment between top-down and bottom-up revenue targets. Consistent driver-based approach to cost planning, particularly around travel costs. Showing if forecast has been entered and how to change/override that number.Any help would be great.

1 month ago

MohanNewly Minted Pigmenteer

asked in Questions & Conversations

Merging dimensions

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 “Text” property so this text property cant perform a lookup to the below dimension or mapping. Kindly let me the know also best way to retrieve the Lead time based on the combination. Dimenison : Mapping Metric : ( This is pulling the data from above chain dimension) Let me know if its clear or not.

1 month ago

Weining BenBudding Pigmenteer

asked in Questions & Conversations

Prorata for overlap period

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. PS: I’m not sure if it’s helpful to create unique ID for all records in the transaction list. We can do it if need. Thanks you in advance for any advice!!

1 month ago

Gabriel OrtizNewly Minted Pigmenteer

asked in Questions & Conversations

Move the first 12 months of MRR to the next years.

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 in a different month than January for example November, we want that the MRR for December and November for that client next year to be the same as it was on the previous year. Therefore I am looking for a formula that allows me to Move only the first 12 months of MRR to the next years. Than you in advance :)Gabriel.

1 month ago

OumaJust Arrived

asked in Questions & Conversations

Alternative of group by in Pigment

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 Latest _subsciption_duration Customer A 24-Jan 3 3 Customer A 21-Jan 12 3 Customer A 23-Jan 4 3 Customer B 24-Feb 12 12 Customer B 23-Feb 12 12

1 month ago

BenjiEmployee

published in Job Board

The FP&A Jobs Report #74 (Walmart, Deel, Amazon...)

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 / Finance Ops Sr. Director 📍Santa Clara, US Hitachi Vantara is a subsidiary of Hitachi Ltd. focused on providing data storage, analytics, and infrastructure solutions for businesses. [SOFTWARE] Associate FP&A Director 📍Newark, US Audible is an audiobook and spoken-word entertainment service owned by Amazon, offering a vast selection of audiobooks, podcasts, and original content that users can stream or download for listening on various devices. [RETAIL] FP&A Director 📍Bentonville, US Walmart Inc. is a multinational retail corporation based in the United States, operating a chain of hypermarkets, discount department stores, and grocery stores. [STAFFING & RECRUITING] Head of Group FP&A 📍Gen

540

1 month ago

Weining BenBudding Pigmenteer

asked in Questions & Conversations

Count number of occurrences and mark the last occurrence

Hello,I currently have the following table, in the first column I fill in tickets manually.And now I have two needs:I hope to count the number of corresponding Tickets through the second column. I hope to mark the last ticket in sequence.I tried using the Rank function, but couldn't achieve these two needs accurately. Can you give me some suggestions?Thank you in advance!!

1 month ago

Thu MaiBudding Pigmenteer

asked in Questions & Conversations

Sparse Engine concept

Hi Pigmenteers!While I’m taking Pigment trainings and explore my personal application, I figure out that there is no information about the size of my application or total number of cells, etc.. and the reason behind is that Pigment is a sparse engine.Therefore, can you help explain to me more about the concept of sparse engine - what is a sparse engine, how does it actually work and what is the benefit compared to non-sparse engine?

1 month ago

Rahul KumarJust Arrived

asked in Questions & Conversations

Using Scenarios in Formula

Hello Everyone, I am working on a headcount report where in I have Actuals and Budget along with the variance. Now I want to reference scenarios in the formulas somehow to create a metric to use this logic: If Actuals is blank and Budget is there, it means that that seat is not filled yet. Is there anyway to do it?

1 month ago

Weining BenBudding Pigmenteer

asked in Questions & Conversations

Can we swap dimension and data of a metric?

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!

1 month ago

MKSMPBBudding Pigmenteer

asked in Questions & Conversations

how to pull the amount for the last non-blank month only

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,

1 month ago

CindyJust Arrived

asked in Questions & Conversations

Show Quarter and Year End Headcount without summing periods

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.

1 month ago

Thu MaiBudding Pigmenteer

asked in Questions & Conversations

Top Tips for modeling in Pigment - Performance Optimisation

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?

1 month ago

Page 2 / 25

Explore the Community

Badge winners

Sign up

Single Sign-On Need help?

Log into the Pigment Community

Single Sign-On Need help?

Scanning file for viruses.

This file cannot be downloaded