Skip to main content

Questions & Conversations

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

  • 796 Topics
  • 2,157 Replies
796 Topics
L
Budding Pigmenteer
LASBudding Pigmenteer
asked in Questions & Conversations
Lookup Property of One Dimension On Another

I have calculate the First Workday on the Day Dimension and want to look up that date on the Month dimension. How can I lookup the First Workday on the Day dimension from the Month dimension. Screenshots below:  

723
Nathan
Employee
P
Newly Minted Pigmenteer
PrarthanaNewly Minted Pigmenteer
asked in Questions & Conversations
Unable to Edit Scenario version

I took a snapshot of an application by selecting the scenario named “Current.” However, when using a table, I am unable to see the edit icon to rename the scenario from "Current" to "fcst." Could you help me understand why the edit option is disabled and how I can enable it? 

472
P
Newly Minted Pigmenteer
C
Newly Minted Pigmenteer
chris.heredia1Newly Minted Pigmenteer
asked in Questions & Conversations
Show calculated item comparing 2 scenarios on a chart while removing the source metric

I want to show a calculated item that takes the difference (A-B) of two scenarios on a chart, but remove the underlying metric that it is calculated from. I have tried using ‘show values as’ vs calculated item but there’s no option to show value as the difference of two scenarios. I also tried duplicating the metric and multiplying the duplicated metric by -1 to offset the initial metric via show value as > difference of metric, but this removes everything including the calculated item. I also tried the filter workaround as explained here, but this also removed everything including the calculated item. Any help here would be appreciated!   

805
C
Newly Minted Pigmenteer
B
Newly Minted Pigmenteer
BasNewly Minted Pigmenteer
asked in Questions & Conversations
Conditional formatting, but only for font color

Hi all, I'm wondering if any of you can help me with some conditional formatting logic. It should be quite simple: I want to create a conditional formatting rule that only changes the font colour and not the fill. The reason is because I already have certain fill colours for the subtotals in my table (i.e.: margin, ebitda, etc.) and I don't want to override the fill colour for these subtotals. So my conditional formatting should only change the font colour instead. Is there any way to do this? I’ve tried using the ‘transparent’ fill colour, but that doesn't help.Any help is much appreciated!

272
B
Newly Minted Pigmenteer
PageJeffrey
Newly Minted Pigmenteer
PageJeffreyNewly Minted Pigmenteer
asked in Questions & Conversations
Rolling TTM stored by Quarter

Hi, I’m trying to create a rolling TTM for Net Income and to store it by Quarter. Rolling TTM stored by quarterI’ve tried using a TTM by Switchover month metric, but that only gives me a single period of data at a time.  However, I would like to show the TTM trend by quarter on a graph so I need to store the amounts by quarter.    Can anyone assist in the best approach for this?  Thanks in advance, Jeff

402
PageJeffrey
Newly Minted Pigmenteer
julian_s
Newly Minted Pigmenteer
julian_sNewly Minted Pigmenteer
asked in Questions & Conversations
Budget Restatement Capability

Hi Gang,We completed the 2025 budget process and archived the data in both a snapshot and saved data metrics across multiple apps. I’ve created a budget restatement capability for OPEX and CAPEX, which was relatively straightforward since it’s essentially a register of debits and credits.However, we’re facing significant challenges in building something similar for workforce (WF) management. The complexity arises from various factors: positions can transfer between departments, new positions can be approved, and we also need to adjust other fields—such as termination dates—to backdate and align with planning done outside of Pigment. WF is particularly challenging due to the downstream calculations involved, including benefits, payroll taxes, bonuses, etc. We can’t simply input new headcount (HC) numbers and dollar values, as we rely on the model to perform these calculations.Has anyone developed a budget adjustment capability for workforce management? If so, what did it look like?

512
julian_s
Newly Minted Pigmenteer
A
Newly Minted Pigmenteer
aaronsolomonNewly Minted Pigmenteer
asked in Questions & Conversations
Annual values do not display when collapsing year dimension

I have a table with data displayed quarterly across the columns. The data in the metric I am using is dimensioned by month, and I am using Month > Quarter as my primary time dimension in the table. I also have a Month > Year dimension stacked on top of the quarter. I use the Sum aggregator on the quarter to display annual totals. When I collapse the year, the totals go away and the entire column is blank. How can I display annual totals when collapsing the year?I have other tables with the same time dimensions/aggregators, and the annual totals display correctly when collapsing the year so this seems like it might be a bug?

485
A
Newly Minted Pigmenteer
A
Newly Minted Pigmenteer
ApurvNewly Minted Pigmenteer
asked in Questions & Conversations
Images from Sharepoint

Hello, Is it possible to add images from sharepoint/onedrive ?I have added the sharepoint link in approved domain, but when I use an Image from the sharepoint, I am unable to view the image in Pigment. Please advise. Thanks,Apurv

1349
Min Li Bright Point
L
Budding Pigmenteer
LASBudding Pigmenteer
asked in Questions & Conversations
Offset N Number of Workdays

 I have a Date Type metric dimensioned by Project and Month. The “Project” Dimension has an Integer Property of “Relative Completion Day”. Number of Days can be positive or negative.I am attempting to calculate a date based on adding “Relative Completion Day” property to the First Workday of the Next Month (M-F). Below is an Example of how it would Ideally Work.What would be the best formula for this? I have also screenshotted my Day dimension. Project Dimension: Calculated Date: (2/3/2025 + (-3) = 1/29/2025) ; 2/3/2025 is the first workday of February - 3 workdays before that.   Day Dimension:  

542
L
Budding Pigmenteer
L
Budding Pigmenteer
LASBudding Pigmenteer
asked in Questions & Conversations
NETWORKDAYS Function Not Calculating Correctly

I am trying to calculate the number of working days in a month using the NETWORKDAYS function but it simply returning the number of days in the month. Am I using this incorrectly?  

382
L
Budding Pigmenteer
J
Newly Minted Pigmenteer
Juned.husainNewly Minted Pigmenteer
asked in Questions & Conversations
Rent Allocation by Start date and end date

Hello All,I currently need a bit of help with allocations. I want to create a rent schedule where the yearly rent amount needs to be allocated based on the yearly contract (start and end date). Just to clarify, one store code can repeat multiple times due to different contract years. Rent type is just used to differentiate between office and stores. 

853
T
Employee
Oliver
Newly Minted Pigmenteer
OliverNewly Minted Pigmenteer
asked in Questions & Conversations
Collapse columns total remains empty

Hi, I have an issue regarding the behaviour of the new expand/collapse columns feature.When I have a summary set to the dimension I want to collapse (Month>Year : LAST for example). If I collapse this dimension the Total column remains empty while it is well displayed when expanded.Do you know why and how to solve this? Thank you  

44605
D
Newly Minted Pigmenteer
L
Just Arrived
lanamaJust Arrived
asked in Questions & Conversations
How to Fill Missing Monthly Data with the Last Available Value for Forecasting?

Hi everyone,I have a dataset with seasonality percentages for different distributors. However, not all distributors have seasonality data for every month, and the available months vary per distributor.Problem:Distributor A has seasonality from January to September 2024. Distributor B has seasonality from January to May 2024. Other distributors have different availability patterns. From October 2024 onward, I need to forecast seasonality using the last October seasonality per distributor.To solve this, I wanted to create a matrix where:Rows = Distributors Columns = Months (January – December, ignoring the year) Each cell = The most recent available seasonality value for that month, from the same month in a previous yearExample Expected Output:Distributor Jan 24 Feb 24 Mar 24 Apr 24 May 24 Jun 24 Jul 24 Aug 24 Sep 24 Oct 24 Nov 24 Dec 24 A 2.1% 2.3% 2.4% 2.5% 2.6% 2.7% 2.8% 2.9% 3.0% 3.1% (Oct 23) 3.2% (Nov 23) 3.3% (Dec 23) B 1.5% 1.

410
L
Just Arrived
B
Newly Minted Pigmenteer
BasNewly Minted Pigmenteer
asked in Questions & Conversations
Replace blanks with average values

Hello everybody,I'm quite new to the community, but I hope you guys can help me with the following. I have a metric that contains budget figures for 2025, dimensioned by stores. However, some stores do not have full year data, but only for half of 2025 (since they will open in June or July and thus they don't have budget data before that). I want to create a metric that calculates the full year effect. So in effect I want to replace months with blank values with the averages of the months that do have data. How can I best go about this?Thanks in advance for your help or for pointing me in the right direction!Best, Bas 

462
B
Newly Minted Pigmenteer
Spandan98
Seasoned Pigmenteer
Spandan98Seasoned Pigmenteer
asked in Questions & Conversations
Automatically adjust Boards to different screen sizes

Hello Pigment community,Is there a way to ensure that Pigment boards automatically adjust to different screen sizes and aspect ratios, so that grids, charts, and other elements remain properly aligned without gaps or distortion?Thanks!

413
F
Employee
Spandan98
Seasoned Pigmenteer
Spandan98Seasoned Pigmenteer
asked in Questions & Conversations
Dynamic Dimension Changes in Metrics – Possible in Pigment?

Hello Pigment Community,I have an interesting question regarding dynamic metric dimensions.Is it possible to modify the dimensions of a metric dynamically based on user input? I understand that we can input values (e.g., Revenue, Cost, etc.) at different hierarchy levels using dependent dropdowns. However, after entering these values, I want all associated calculation metrics to automatically adjust to the selected dimensions.For example:I input data at L2: Country (with parent L1: Region) and L2: Industry (with parent L1: Industry Type and child L3: Sub-Industry). Using dependent dropdowns, users can enter values at the correct hierarchy. I want all relevant calculation metrics to dynamically align with L2: Country and L2: Industry based on the selected input.Can this be achieved in Pigment? If not, what would be the best way to request this functionality?Looking forward to your guidance. Thanks!

361
F
Employee
Y
Newly Minted Pigmenteer
YaejisNewly Minted Pigmenteer
asked in Questions & Conversations
Scenario

Hello,  I am trying to use the Scenario tool and need some help. Goal is to show both “Current” and “AOP” scenarios so I can compare.  Both scenarios are checked “Current” scenario shows as below but “AOP” scenario does not show even when I changed the formula to reference another metric The “AOP” scenario values show when I drop in the AOP metric, but it is in “Current” Scenario (red). Do I need to drop in the AOP metric in the Pivot Metrics?  

652
Y
Newly Minted Pigmenteer
A
Just Arrived
ASLJust Arrived
asked in Questions & Conversations
Automatically deactivate users who leave company

Is there a way to automatically deactivate Pigment users that have left the company?  

381
M
Employee
W
wyunngOccasional Observer
asked in Questions & Conversations
How to Implement a Rolling XNPV in Pigment?

I’m looking for a way to calculate a Rolling XNPV in Pigment, but I see that only the standard XNPV function is available.Here’s what I need:The standard XNPV in Pigment calculates the NPV based on all future cash flows from a specific date onward, considering the given discount rate. However, I need to dynamically roll the NPV forward each year, meaning: In 2024, the NPV should only consider cash flows from 2025 onwards. In 2025, the NPV should only consider cash flows from 2026 onwards, and so on. Each year's NPV should exclude past cash flows and focus only on the future values starting from the next period. Has anyone implemented something similar in Pigment, or is there a workaround to achieve this rolling effect?Would appreciate any insights or alternative approaches!

612
W
Y
Newly Minted Pigmenteer
YaejisNewly Minted Pigmenteer
asked in Questions & Conversations
Circuler Reference

Hello, can you please help with the circular reference?For the “Revenue/Seller”, it needs to reference prior week line and current week WoW% input.I tried using this formula referencing the document here but did not work (used SELECT and PREVIOUS).For the Week 8 result, it should be exact same as Week 7.  IF(Week.'Start Date' <= '[Live] Forecast Start Date','[Live - Weekly] Revenue / Seller Final', '[Live - Weekly] Revenue / Seller Final'[SELECT: Week-1] * '[Live - Weekly] Revenue / Seller Final WoW') Thank you!  

1237
Y
Newly Minted Pigmenteer
V
Newly Minted Pigmenteer
VikatzenNewly Minted Pigmenteer
asked in Questions & Conversations
Identify duplicated Items in a metric

Hi, I have a table which I use to define a marketing channel based on department, account, and vendor. I want to make sure that I do not have duplicated entries for different marketing channels. Basically, that the same combination of department, account, and vendor does not exist. The trick here is that sometimes, there is no vendor or account defined. All four metrics are dimensioned by “line” and a corresponding dimension (line, department / line, account). I was thinking of creating a text metric (mgkchannel_key), which accumulates the combination of department, account, and vendor, and then counting the unique occurrences for each value. For example, for row 19 and 24, I would see another metric with the value 2, everything else would be a 1. (Then creating a boolean and use conditional formatting to highlight duplications).  How can I count the unique values in a metric? The solution I received below did not work since there is not always a defined vendor or account.  Solution th

692
V
Newly Minted Pigmenteer
A
Newly Minted Pigmenteer
AndrewRabinesNewly Minted Pigmenteer
asked in Questions & Conversations
Grab the most recent Load Date by Scenario from a transaction list with multiple load Dates per Month

I have a Workday load with multiple loads per month and I want to be able to pull into a metric, the individual fields by load date and by scenario. I have two scenarios with one set to Feb 2 and one set to Feb 15, when I test the following logic: EE_Load_Workday_Actuals.Department[by lastnonblank: EE_Load_Workday_Actuals.'Workday Employee', TIMEDIM(EE_Load_Workday_Actuals.'Load Date',Month)][by lastnonblank: timedim('Set_Last Actuals WD Load Date',Month)]  , the metric seems to just pull the lastnonblank literally from the transaction list. I changed and moved a sample in-month change to department for one record and it just took whichever record was physically lower down in the transaction list, rather than the load date.Is there a better way to go about this without the timedim and lastnonblank functions and modifiers? I couldn’t find anything else in the Community and couldn’t think through another approach. If there is a more efficient way to use a date within a modifier to get Pi

903
A
Newly Minted Pigmenteer
Spandan98
Seasoned Pigmenteer
Spandan98Seasoned Pigmenteer
asked in Questions & Conversations
How to Dynamically Manage Hierarchies and Input Data from a Board in Pigment?

Hello Pigment Community,I’m looking for a way to create, delete, and select hierarchies directly from a board while allowing users to input values at specific levels.Context:We have multiple hierarchical dimensions: Accounts (2 Levels) L1: Account Groups L2: Accounts Geography (3 Levels, Parent of Accounts) L1: Region L2: Country L3: Area Organization (2 Levels, Parent of Geography) L1: Organization_1 L2: Organization_1.1 Use Case:Users have revenue data and he wants to input data at this level:L1: Organization_1 L2: Country L2: AccountsI want to provide an interactive board where users can:✅ Select the Organization Level, Geography Level, and Account Level for data input.✅ Enter data at the chosen levels, ensuring calculations use only the selected hierarchies.✅ Add new parallel hierarchies (e.g., Industry Level as a child of Organization) dynamically from the board.Question:How can I set this up in Pigment to allow users to manage hierarchies and input data flexibly?Would lov

1043
Min Li Bright Point
Ola
Seasoned Pigmenteer
OlaSeasoned Pigmenteer
asked in Questions & Conversations
Trigger import through API without sending data

Is it possible to trigger an import from Netsuite, Amazon S3 or Salesforce using Pigment’s API?So in this case we don’t want to send the data in the request body, we just like to trigger an existing import using the API instead of having the import to run on a schedule. Just want to confirm if this is supported or not, can’t really tell by reading the documentation here:  

3614
G
Newly Minted Pigmenteer
V
Just Arrived
Vinoth RavichandranJust Arrived
asked in Questions & Conversations
Copy Pigment Board to another Application

I have created a board in an application that includes all the necessary dashboards and tables. However, I do not want to grant access to the entire application for all users in the organization. Instead, I plan to create separate applications for each department and replicate the board within each of these applications, sharing it only with the respective department heads. Could you advise if there is a way to duplicate or copy a board to a different application?

543
Keiran
Community Manager

Badge winners

  • Pigment Like a Pro
    Nusaybahas earned the badge Pigment Like a Pro
  • Pigment Modeling Fundamentals Certified
    Nusaybahas earned the badge Pigment Modeling Fundamentals Certified
  • Read and Interpret Formulas Certified
    Seydou Kanehas earned the badge Read and Interpret Formulas Certified
  • Read and Interpret Formulas Certified
    Keiichi Tsuchiyahas earned the badge Read and Interpret Formulas Certified
  • Read and Interpret Formulas Certified
    Teo Storkhas earned the badge Read and Interpret Formulas Certified
Show all badges
Terms & ConditionsCookie settings