Skip to main content

Questions & Conversations

Got a question about your models? Want to start a discussion? It's here!

  • 682 Topics
  • 1,841 Replies
682 Topics
G
Newly Minted Pigmenteer
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? 

330
G
Newly Minted Pigmenteer
9 months ago
P
Budding Pigmenteer
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. 

1282
P
Budding Pigmenteer
9 months ago
Thu Mai
Thu MaiKeen Learner
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? 🤔

1322
Thu Mai
9 months ago
D
dariousKeen Learner
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

2281
Issam Moalla
Employee
9 months ago
M
Newly Minted Pigmenteer
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.

2162
M
Newly Minted Pigmenteer
9 months ago
W
Seasoned Pigmenteer
Weining BenSeasoned 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!!

1652
W
Seasoned Pigmenteer
9 months ago
G
Newly Minted Pigmenteer
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.

2493
G
Newly Minted Pigmenteer
9 months ago
O
Just Arrived
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

1401
Benoit
Community Manager
9 months ago
Thu Mai
Thu MaiKeen Learner
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?

1783
Thu Mai
9 months ago
W
Seasoned Pigmenteer
Weining BenSeasoned 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!

1396
W
Seasoned Pigmenteer
9 months ago
M
Budding Pigmenteer
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,  

23510
M
Budding Pigmenteer
9 months ago
C
Newly Minted Pigmenteer
CindyNewly Minted Pigmenteer
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.

761
Issam Moalla
Employee
9 months ago
Thu Mai
Thu MaiKeen Learner
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?  

1722
Thu Mai
9 months ago
A
Newly Minted Pigmenteer
Antoine-2Newly Minted Pigmenteer
asked in Questions & Conversations
Dependent dropdown in a form

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.

1161
Thu Mai
9 months ago
Y
Just Arrived
yplinJust Arrived
asked in Questions & Conversations
How do I turn my variance to points vs bps

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

662
Thu Mai
9 months ago
A
aseghirOccasional Observer
asked in Questions & Conversations
Error message display image

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

1145
A
9 months ago
J
Just Arrived
justinspielmanJust Arrived
asked in Questions & Conversations
Creating quarter-end / year-end summaries of cumulative metrics

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!

2362
Issam Moalla
Employee
9 months ago
N
Budding Pigmenteer
Naomi VriendBudding Pigmenteer
asked in Questions & Conversations
How can we get a day dimension to stop showing date and time?

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 

1452
Issam Moalla
Employee
9 months ago
E
Seasoned Pigmenteer
ElodieSeasoned Pigmenteer
asked in Questions & Conversations
Formula error - timed out

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

1553
E
Seasoned Pigmenteer
9 months ago
R
Just Arrived
RohitDivate_GustoJust Arrived
asked in Questions & Conversations
Has anyone tried integrating CAPIQ with Pigment for public company benchmarking?

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.

1121
CamilleMomo
Seasoned Pigmenteer
9 months ago
Cat M
Cat MFrequent Reader
asked in Questions & Conversations
Using IF and AND function together in a formula?

Hi, I want to use the AND function together with the IF function in one formula in a property of a transaction list. Is this possible in Pigment? I can’t seem to get the AND part to be recognised when using it as part of the logical test of an IF statement, the way you can in Excel. In Excel for example I can write an IF statement using AND:=IF(X2>7,"check",IF(AND(E2="Direct",M2="Retail O&O",X2>0),"check","OK")) But in Pigment when I type AND as part of my IF statement, the function doesn’t seem to be recognised and my formula doesn’t work. The text for ‘AND’ doesn’t show up in green the way it does for the IF function (or other functions.) I don’t have any issues using the IF function otherwise, or nesting multiple IFs in a formula, it’s just that I can't seem to use IF with AND. Please let me know if it’s possible, or if perhaps I need to write it in a different way? If not possible I suppose I can create a separate property to do the AND check. Thanks,Cat

2662
Cat M
10 months ago
M
Seasoned Pigmenteer
mmusabSeasoned Pigmenteer
asked in Questions & Conversations
Calculating forward sum of values

HiI want to sum the values from my current starting point to the last value available in a metric. As you can see first value is 0.22 and last is -30,993,214 and sum of all those in middle will be 30,669,213 something.that metric called inp_when to sell asset differs for each sage entity asset. How can i go about doing this calc in simple way? I tried moving sum etc but doesnt work. 

3364
M
Seasoned Pigmenteer
10 months ago
E
Seasoned Pigmenteer
ElodieSeasoned Pigmenteer
asked in Questions & Conversations
Modeling lock budget

Hello,I have created the budget based on historical data (budget version based on actuals version up to a switchover date), but at each closing period and when we import the new data, the historicals can vary so it makes my whole budget move… The uploaded data for the actuals is imported into several transaction and dimension lists.It is not optimal as the budget should be closed as of now and not moving. Has anyone encountered the same issue? The idea would be to be able to use the budget version in comparison tables so it needs to be fixed in the app.Hope this makes sense!Thanks,Elodie

36211
E
Seasoned Pigmenteer
10 months ago
M
Budding Pigmenteer
MKSMPBBudding Pigmenteer
asked in Questions & Conversations
Applying a specific months value to all months in a year

Hey,I am trying to reference the headcount for December and apply it to all months in the relevant financial year as part of a calculation. i.e if head count in Dec 24 in 100 and Dec 25 is 150, the formula would show 100 for all months in 2024 and 150 in all months of 2025.Has anyone done anything like this before and found a solution? Thanks! 

2191
francois
Employee
10 months ago
Y
Newly Minted Pigmenteer
YaejisNewly Minted Pigmenteer
asked in Questions & Conversations
Saving calculation

Hi! Can someone please help with setting up the formula for below? Thanks in advance!----Dates:Given Data (yellow highlighted) Question: I am seeing for formula to get the result below yellow highlighted row, “Result”.  The calculation starts from 3rd months from the start date, 10/31/2022. So it needs to calculate “January value 18,792 minus start date value 18,792. The result for 2023 Jan is zero. Then, calculation is same concept but moves forward. It’s always deducting the start date value, 18,792.  For July 2023, calculation is July 2023 19,355 minus the start date value 18,792, resulting 564.The calculation should stop in Bridge end date, 12/31/2023.   Thank you!

3003
Y
Newly Minted Pigmenteer
10 months ago

Badge winners

  • Pigment Like a Pro
    Jordan C.has earned the badge Pigment Like a Pro
  • Read and Interpret Formulas Certified
    Wafaehas earned the badge Read and Interpret Formulas Certified
  • Pigment Modeling Fundamentals Certified
    luciemaheyhas earned the badge Pigment Modeling Fundamentals Certified
  • Pigment Like a Pro
    Wafaehas earned the badge Pigment Like a Pro
  • Innovator
    Ehabhas earned the badge Innovator
Show all badges
Terms & ConditionsCookie settings