Skip to main content

Questions & Conversations

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

  • 682 Topics
  • 1,842 Replies
682 Topics
Spandan98
Budding Pigmenteer
Spandan98Budding Pigmenteer
asked in Questions & Conversations
Data Access rights for specific columns in Pigment

Dear Pigment Community,I hope you're doing well!I am currently facing two challenges in my Pigment setup and would greatly appreciate your guidance: Freezing Data Entry for Weekends & Holidays: I would like to restrict data entry for weekends (e.g., 06/01/2024 and 07/01/2024) and holidays (which I will updating in a different dimension) while keeping data entry open for other days. This restriction should apply consistently for all weekends throughout the year. Customizing Date Formats in Column Headers: I want to display the date in the column header in the format: 6th Jan 2024 and 7th Jan 2024 (instead of the default format). This format should be applied consistently across all dates throughout the year. If anyone has suggestions, solutions, or best practices for implementing these features, I would greatly appreciate your input!Thank you in advance for your help! 

231
Matt D
Employee
3 days ago
Spandan98
Budding Pigmenteer
Spandan98Budding Pigmenteer
asked in Questions & Conversations
Date and Employee Mapping in Pigment

Hello Pigment community,I want to map the two tables such that I can map the sales of manager mapped according to their end date and start date, and list their sales according to the employees under them.I am attaching the assignment screenshot below, please kindly help me.Thanks!  

181
Keiran
Community Manager
7 days ago
X
Just Arrived
Xia HeJust Arrived
asked in Questions & Conversations
Unable to find the block to complete the activity

 

341
Julie Guérin
Employee
7 days ago
A
Budding Pigmenteer
AdamBudding Pigmenteer
asked in Questions & Conversations
Circular adjustment

Hi, I have the following challenge:Metric 1. simple metric with sales forecast in units with one dimension only MONTH. So let’s say it is Jan: 20; Feb: 30; Mar: 20; Apr: 15 Metric 2. active orders already made e.g. 35 with no dimension at all, just one number.  Now I need to adjust Metric 1 with Metric 2 applying the rule that earlier months are adjusted as first. I.e. adjusted forecast would be: Jan: 0; Feb: 15; Mar: 20; Apr: 15How do I do it in Pigment? Regards,Adam

401
Keiran
Community Manager
11 days ago
Spandan98
Budding Pigmenteer
Spandan98Budding Pigmenteer
asked in Questions & Conversations
Creating a working calendar with holidays marked in Pigment

Hello Pigment community,How can I create a working calendar with holidays marked as the attached image below.Please kindly help me in this project. I hope you will do the needful. Thanks!Thanks! 

243
francois
Employee
11 days ago
Spandan98
Budding Pigmenteer
Spandan98Budding Pigmenteer
asked in Questions & Conversations
Count number of duplicate items in metric

Hello Pigment Community,I’m currently facing an issue with counting duplicate items in Pigment. As shown in the image below, I’ve successfully extracted words from a sentence. Now, I’d like to conditionally format and highlight repeated words starting from their second occurrence.I understand that using a counter to track how often a word appears is key here, but I’m struggling to come up with the correct formula to achieve this.Could someone kindly guide me on how to proceed? Your help would be greatly appreciated!Thanks in advance!  

341
CDALMAY
13 days ago
Ola
Seasoned Pigmenteer
OlaSeasoned Pigmenteer
asked in Questions & Conversations
Combine "Show value as" with aggregations

We have created an income statement where we have one metric for Income and another metric for COGS. Both these metrics is by the dimension Account and in our table we use Account Group and Account on the Rows which gives us a  dynamic income statement where it is possible to expand and collapse rows to see the hierarchy:- Income      - Account Group              -AccountOur challenge is that we like to have the exact same view that shows us Income and COGS per Order. We can achieve this by using “Show value as” to show our metrics in relation to total number of orders and it also works fine when filtering the table by Location or Subsidiary. However, show value as does not work in combination with grouping by Account Group and Account, since number of  orders is not by account. If we add the dimension Account to our metric for Number of  orders we can get it right on the lowest level, but in this case the aggregations by account and account group becomes wrong. Is there any workaround

994
E
Newly Minted Pigmenteer
13 days ago
Spandan98
Budding Pigmenteer
Spandan98Budding Pigmenteer
posted in Questions & Conversations
Resources to become an expert in Pigment

Hello Pigment Community,I’m new to Pigment and eager to become an expert in using the platform. To accelerate my learning journey, I would greatly appreciate your help with the following:Practice Assignments: A list of exercises or projects, starting from beginner to advanced levels, that I can work on to build and refine my Pigment skills. Video Tutorials: Resources or video walkthroughs demonstrating how to implement large-scale projects in Pigment.Having access to these resources would significantly enhance my understanding and hands-on experience.If anyone can point me in the right direction or share relevant materials, I’d be incredibly grateful! Looking forward to learning from this amazing community.Thank you so much for your time and support!

485
Spandan98
Budding Pigmenteer
13 days ago
H
Newly Minted Pigmenteer
HarshilNewly Minted Pigmenteer
asked in Questions & Conversations
Dynamic Variable Not Updating by Scenario

I created a dynamic variable called “TvA Actuals Range” and set it equal to the “IsActuals” date range. The issue is that the date range is not updating based on the scenario. Can you please advise?

111
CDALMAY
13 days ago
JDLove
Newly Minted Pigmenteer
JDLoveNewly Minted Pigmenteer
asked in Questions & Conversations
Hub Shared Dimensions not showing in Application Metric Settings

Basic newbie question, I have a sample Hub that has the shared dimensions, I am building a test application but I cannot seem to get the shared dimensions to show as options in the Metric Settings. I can see the Hub Dimensions are showing as shared.I can see the Hub Application is showing as shared.Thanks JDLove 

182
JDLove
Newly Minted Pigmenteer
16 days ago
Spandan98
Budding Pigmenteer
Spandan98Budding Pigmenteer
asked in Questions & Conversations
Quarter and Year dimensionality problem

How to add a grid on a board with input fields? For example I have data for quarters and I want to set targets of a year which can be entered by the user on a board. How can I achieve this?

252
Spandan98
Budding Pigmenteer
17 days ago
Y
Newly Minted Pigmenteer
YaejisNewly Minted Pigmenteer
asked in Questions & Conversations
Amortization based on start/end dates

Hi Pigment team! I am trying to setup a amortization schedule as following. Can you please help?Orange is given resources Green is the output that I would like to have in the table/metric Blue is just validation for myself which aligns with the orange Total column and doesn’t need to be in the model   Following is the formula I used in excel:For 1/31/2023 Column alone, formula is  =($V5/$AS5)*((AV$4-$AQ5)+1) For 2/28/2023 and onwards, formula is  =IF(EOMONTH($AR264,0)<AW$4,0,IF(AW$4<=$AR264,($V264/$AS264)*(TEXT(AW$4,"d")),($V264/$AS264)))   I tried using the following formula in playground for 1/31/2023 column, but it doesn’t seem to work. IF(Month.'Start Date'=DATE(2023,01,1),('RSU Accounting Data'.'Expense Amortized Future'/'RSU Accounting Data'.'Amortization # of Days') * DAY(Month.'End Date')) Thanks for helping out!

2717
JDLove
Newly Minted Pigmenteer
17 days ago
B
BenedikteOccasional Observer
asked in Questions & Conversations
Error: Unknown Block (List or Metric)

Hi,I am getting an error in my metric that is saying that block that I am referring to is not existing, but in fact it is a block that I am using actively in my management reporting. So I do not understand why it seems like the Block is not possible to retrive.Can someone please help me out regarding this? :)Best regards,Benedikte 

212
CDALMAY
18 days ago
M
Newly Minted Pigmenteer
MaxenceGoigNewly Minted Pigmenteer
asked in Questions & Conversations
How to apply a recurring calculation on a metric with a monthly dimension ?

Hi everyone,I need help applying a recurring calculation to a monthly metric in Pigment.Here’s the situation:I have a metric called RES_Active Doctors, and I want to apply month-by-month the percentage stored in IN_Churn Existing Doctors - 3YP. The calculation should use the value of the previous month as the base.For example:If the initial value of RES_Active Doctors in Month 1 is 100, and the percentage in IN_Churn Existing Doctors - 3YP is 10%: Month 1: 100 (initial value) Month 2: 90 (100 - 10%) Month 3: 81 (90 - 10%) Month 4: 72.9 (81 - 10%) And so on. I’m wondering how to model this type of calculation in Pigment. Is there a function or method to automate this logic by referencing the previous period?Thanks a lot for your help! 😊

522
M
Newly Minted Pigmenteer
18 days ago
P
Seasoned Pigmenteer
pigment_enthusiastSeasoned Pigmenteer
asked in Questions & Conversations
RANK function - How to handle ties

I have a metric : which has 20, 20, 20 and 15, 11 numbers I need to show Top 2 of the products: It only shows the 2 products with 20 valueIt should show all those  products with 20,20, 20 and 15 value20 is a tie number. 

10414
W
Seasoned Pigmenteer
19 days ago
W
Newly Minted Pigmenteer
William Murray BrownNewly Minted Pigmenteer
asked in Questions & Conversations
Dimensions of "seasonality" argument for SEASONAL_LINEAR_REGRESSION

The documentation for the SEASONAL_LINEAR_REGRESSION function states that the “seasonality” argument can have the following dimensions:No Dimension or Dimensions of Text 1I have two questions about this…When I try to use a metric with no dimensions as this argument, I get the error “Function SEASONAL_LINEAR_REGRESSION requires argument 2 to be a positive integer”. What am I doing wrong? The metric where I’m using SEASONAL_LINEAR_REGRESSION has three dimensions (month and two non-time dimensions). What does “Dimensions of Text 1” mean?

659
W
Newly Minted Pigmenteer
19 days ago
D
Community Manager
DamianAtPigmentCommunity Manager
posted in Questions & Conversations
Pigment API Windows Powershell examples

 Examples of how you can import data to, or export data from Pigment using the Pigment API and Windows PowerShell. 1. Generate Pigment Import/Export API keyhttps://community.pigment.com/security-permissions-82/manage-api-keys-226 2. For Imports into Pigment (to understand the process)https://community.pigment.com/importing-and-exporting-data-95/how-to-trigger-an-import-with-apis-230 3. For Exports from Pigment (to understand the process)https://community.pigment.com/importing-and-exporting-data-95/how-to-export-data-from-pigment-with-apis-229 4. To import data using PowerShell# Define the API endpoint and credentials$API_IMPORT_KEY = 'YOUR_PIGMENT_IMPORT_API_KEY'$API_IMPORT_URL = 'https://pigment.app/api/import/push/csv?configurationId='$PIGMENT_IMPORT_CONFIG = 'YOUR_IMPORT_ID'$PIGMENT_IMPORT_PAYLOAD = 'test.csv'# Set up the headers for the API request$headers = @{ "Authorization" = "Bearer $API_IMPORT_KEY" "Content-Type" = "text/csv"}Write-Output "Now trying the import into $PI

250
D
Community Manager
20 days ago
Dmitrii
Seasoned Pigmenteer
DmitriiSeasoned Pigmenteer
asked in Questions & Conversations
Month cohort number calculation

I want to calculate the month count starting from the first revenue month for each tenant. I am trying this formula:MONTHDIF(Tenant.cohort_invoice_M.'Start Date', Month.'Start Date')But I am getting an errorFunction MONTHDIF arguments have incompatible Dimensions: Dimensions of argument 1 must be a subset or superset of Dimensions of argument 2 How to fix this?Tenant.cohort_invoice_M has type dimension Month.

302
Dmitrii
Seasoned Pigmenteer
21 days ago
A
Just Arrived
AravindJust Arrived
asked in Questions & Conversations
Changing data type of metrics

Hi All,I’m not able to change the data type of ‘User Roles’ metric from Role(local) to Role(Data hub). But when i try to change the data type, I’m getting the following error. Pls advise on this.“Unable to change the structure of a metric used in the security settings. Remove it from the security settings before performing this action.”How do i overcome this error?

311
oliverlee
Seasoned Pigmenteer
21 days ago
E
Newly Minted Pigmenteer
EvlimeNewly Minted Pigmenteer
asked in Questions & Conversations
I want to set up a dimension from a calculation

Hello community, I am creating a PnL report for our company. At the moment it displays revenue (metric), cost (another metric), gross profit ( metric from formula) and so on. The metrics are derived from a transaction table that I imported.Now I would like to show the total USD and in the coloumn next to it I want to display avg per person. I have calculated the Avg per person and managed to create the same table as above from the avg metrics of the above metrics. BUT because these are metrics, I cannot have them side by side in the table. If I add the avg per person to my total table it will just add all the rows below. I can move upp the “metric” in pivot to columns but then that messes up the revenue, costs, gross profit placement etc.I created a dimension called version and was able to attach that as a property to the total metrics and the avg metrics, but haven’t found a way to union the two metrics with the variant dimensions. So my question is, how can I create a dimension from

533
E
Newly Minted Pigmenteer
25 days ago
D
Community Manager
DamianAtPigmentCommunity Manager
posted in Questions & Conversations
MySQL RDBMS import into Pigment

Often, we get the request to load enterprise volume data from MySQL RDBMS into Pigment. While Pigment doesn’t have a dedicated MySQL connector or ODBC capability, this can easily be achieved via Pigment’s import API. Below is example code that connects to MySQL, executes a SQL statement, and imports the data into Pigment. 1. Generate Pigment Import API keyhttps://community.pigment.com/security-permissions-82/manage-api-keys-226 2. For Imports into Pigment (to understand the process)https://community.pigment.com/importing-and-exporting-data-95/how-to-trigger-an-import-with-apis-230 3. Load Data from MySQL RDBMS into Pigment via Pigment’s Import API using Python:import mysql.connectorimport getpassimport requestsfrom io import StringIOimport sysimport logging# Configure logginglogging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')# MySQL Connection & SQL queryusername = 'MYSQL_USERNAME'server = 'MYSQL_SERVER'database = 'MYSQL_DATABASE'password = g

120
D
Community Manager
26 days ago
D
Community Manager
DamianAtPigmentCommunity Manager
posted in Questions & Conversations
Pigment API and Microsoft Excel (Pull & Push) examples

Microsoft Excel imports/exports can be achieved through using the Pigment Import/Export API and Excel’s VBScript. It will need the following information: 1. Generate Pigment Import/Export API keyhttps://community.pigment.com/security-permissions-82/manage-api-keys-226 2. For Imports into Pigment (to understand the process)https://community.pigment.com/importing-and-exporting-data-95/how-to-trigger-an-import-with-apis-230 3. For Exports from Pigment (to understand the process)https://community.pigment.com/importing-and-exporting-data-95/how-to-export-data-from-pigment-with-apis-229 4. The Excel work  First enable Developer Tab in Excel Open Excel. Go to File > Options. In the Excel Options dialog box, click Customize Ribbon. Check the Developer checkbox and click OK. Create a VBA Macros Go to the Developer tab and click Visual Basic. In the VBA editor, insert a new module: Right-click on any existing module or the workbook name in the Project Explorer.

340
D
Community Manager
28 days ago
D
Community Manager
DamianAtPigmentCommunity Manager
posted in Questions & Conversations
Microsoft SQLServer RDBMS import into Pigment

Often, we get the request to load enterprise volume data from Microsoft SQLServer RDBMS into Pigment, whilst Pigment doesn't have a dedicated SQLServer connector or ODBC capability, this can easily be achieved via Pigment's import API. Below is example code that connects to SQLServer, opens a cursor, executes a SQL statement and imports the data into Pigment. 1. Generate Pigment Import API keyhttps://community.pigment.com/security-permissions-82/manage-api-keys-226 2. For Imports into Pigment (to understand the process)https://community.pigment.com/importing-and-exporting-data-95/how-to-trigger-an-import-with-apis-230 3. To load data from Microsoft SQLServer RDBMS directly into Pigment via Pigment's Import API using Pythonimport pyodbcimport getpassimport requestsfrom io import StringIOimport sysimport logging# Configure logginglogging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')# SQL Server Connection & SQL queryusername = 'SQLSERVER_USERNAME

330
D
Community Manager
28 days ago
D
Community Manager
DamianAtPigmentCommunity Manager
posted in Questions & Conversations
Oracle RDBMS import into Pigment

Often, we get the request to load enterprise volume data from an Oracle RDBMS into Pigment, whilst Pigment doesn't have a dedicated Oracle RDBMS connector or ODBC capability, this can easily be achieved via Pigment's import API. Below is example code that connects to Oracle, opens a cursor, executes a SQL statement and imports the data into Pigment. 1. Generate Pigment Import API keyhttps://community.pigment.com/security-permissions-82/manage-api-keys-226 2. For Imports into Pigment (to understand the process)https://community.pigment.com/importing-and-exporting-data-95/how-to-trigger-an-import-with-apis-230 3. To load data from an Oracle RDBMS directly into Pigment via Pigment's Import APIimport oracledbimport getpassimport requestsfrom io import StringIOimport sysimport logging# Configure logginglogging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')# Oracle Connection & SQL queryusername = 'ORACLE_USERNAME'connect_string = 'ORACLE_CONNECT_STRI

310
D
Community Manager
28 days ago
T
Newly Minted Pigmenteer
Ta-WingNewly Minted Pigmenteer
asked in Questions & Conversations
How to set up a Map chart - Exclude countries

Exclude CountriesI’m trying to show the Revenue by Country and where countries have nil/zero revenue, how would you Exclude them so even the name doesn’t pop up? I’m wondering is it because my main data set has all countries with the longitude/latitude.

472
T
Newly Minted Pigmenteer
1 month ago

Badge winners

  • Read and Interpret Formulas Certified
    qcassidyhas earned the badge Read and Interpret Formulas Certified
  • Pigment Modeling Fundamentals Certified
    qcassidyhas earned the badge Pigment Modeling Fundamentals Certified
  • Pigment Like a Pro
    qcassidyhas earned the badge Pigment Like a Pro
  • Read and Interpret Formulas Certified
    jeff_isaacsonhas earned the badge Read and Interpret Formulas Certified
  • Pigment Like a Pro
    Raphael Chas earned the badge Pigment Like a Pro
Show all badges
Terms & ConditionsCookie settings