Skip to main content

Questions & Conversations

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

  • 814 Topics
  • 2,196 Replies
814 Topics
J
Seasoned Pigmenteer
JoseSeasoned Pigmenteer
asked in Questions & Conversations
Using something similar to the formula Today() in Excel

Dears, I am trying to filter by the current month in each of the metrics, I would use TODAY() function in excel, but since there is no function like that on Pigment. As a workaround I created a metric that I called Current Date (Month Dimension) , which I will update monthly, so that I can filter by that metric and automatically update all the other metrics. IF('MRR Be Churn Analysis'=1,TRUE)[FILTER:'Data Hub'::Month='Current Date'] However, I did not work, do you have any suggestion or guidance? Thank you very much in advance! Jose

7948
E
Newly Minted Pigmenteer
L
Newly Minted Pigmenteer
LoliNewly Minted Pigmenteer
asked in Questions & Conversations
Weighted average

Hi!If I have this info in several rows, as a list of transactions:One column with the amount in a local currency One column with the amount in EUR One column which tells me the month of the transactionHow can I calculate the FX rates per month, as a weighted average (not the simple average, but the average taking into account the weight of each transaction in the total amount of that month)?Sorry maybe is so obvious how to do it, but I don’t know, I know how to do it exporting the transaction list and calculating it in the file but...not sure how to calculate it directly in Pigment, which is what I would like to do better. Thank you!

8297
G
Newly Minted Pigmenteer
Roy Stolero
Budding Pigmenteer
Roy StoleroBudding Pigmenteer
asked in Questions & Conversations
Period Type in Different Scenarios

Hi Team,We have been trying to figure out how to visualize and separate actuals and forecast data when sharing rolling forecast scenarios with budget owners and other users, mainly because non-FP&A users are not familiar with the switchover date workflow. We bring this up because the period type is a cross scenario setting, and we set different switchover dates for different scenarios so it won’t help.What’s the best practice? Roy and the rest from Melio :)

11279
CDALMAY
M
Newly Minted Pigmenteer
M_OlivierNewly Minted Pigmenteer
asked in Questions & Conversations
Dashboarding

I want to make a welcome message for my dashboard but I want it to be big enough to be used on my dashboard.Any help would be appreciated.This is how it looks like right now. 

2011
Benoit
Community Manager
G
Newly Minted Pigmenteer
Grégoire LeveilléNewly Minted Pigmenteer
asked in Questions & Conversations
Keeping only the first non blank value of a metric

Hello Pigment Community, I want to have a metric that has clients in lines and months in columns and that is filled with 1 only for the first month the client bought a thing. So in the end, on each line there should be only one 1 in the month of the first buy. For the moment I created a metric with clients in line and months in columns that is filled with 1 for all the months when the client bought a thing. My idea was to use this metric and the firstnonblank modifier but it does not work… Could you help me please ? 

4946
Benoit
Community Manager
D
dariousKeen Learner
asked in Questions & Conversations
Inconsistent Results in Pigment Math Output - Seeking Insights and Resolution

Hi Pigment Community,I am reaching out to bring attention to an issue I've been encountering with Pigment, particularly in relation to the mathematical output it provides. While Pigment generally performs as expected, I have noticed instances where the results are unexpectedly incorrect.To provide clarity, I have attached screenshots displaying the metrics and their corresponding outcomes. I've highlighted the specific instances where Pigment has generated results that deviate from the anticipated values. It's worth noting that there are occurrences where the formula yields accurate results, but the inconsistency raises concerns about best practices and how formulas work. My hope is that you can help me understand the root cause of this issue and provide insights to correct this issue. I appreciate any assistance you can provide in shedding light on why Pigment might be producing these unexpected results. Additionally, I am keen to learn any best practices or recommendations to avoid s

2732
Mat
M
Just Arrived
mamoudJust Arrived
asked in Questions & Conversations
Intercompany

What are the best pratices for intercompany process (Eliminate inter-company transactions )

2461
Benoit
Community Manager
Dave L
Newly Minted Pigmenteer
Dave LNewly Minted Pigmenteer
asked in Questions & Conversations
Excel connector - retain formatting?

Is there a way to retain number formatting in excel when using the Pigment Excel Connector?  I can retain things such as cell borders, colors, etc. but cannot retain number formatting.  Every time I pull data to refresh from Pigment, the number formatting is overridden in my excel workbook.This is not the case with the Google Sheets connector, which does retain number formatting in cells where Pigment data is being imported.  Does this functionality exist for the Excel connector, or is this a feature request?  

1632
Dave L
Newly Minted Pigmenteer
D
dariousKeen Learner
posted in Questions & Conversations
Automated Forecast (Item Variable vs Period type filter)

Hi Pigment Community,I’m currently working on building a conversion rate forecast in pigment and wanted to hear your thoughts on my calendar setup. Context: In our forecast model we are interested only in the current year and previous year. Information older than that is not being included in our forecast, visually and mathematically. Thus, I wanted to have an automated rolling calendar on an annual basis. This way if we are in Oct 2023 my model will show Jan 2022 - Oct 2023, rather than Oct 2022 - Oct 2023.I know that in pigment you can use item variables to easily update variables, such as year, for all views in a model. However, I have opted for a different solution. Instead I have tweaked the ‘Period type’ dimension to include a 3rd item called ‘Actual old’. I then filter my metrics on all data where period type does not equal ‘Actual old’. Additionally, my period type is decided by my switch date, which is automatically chosen based on the max date in my data transaction.Current p

1600
D
A
AdamOccasional Observer
asked in Questions & Conversations
MOVING...MULTIPLICATION

Dear All, How to construct a formula for moving multiplication? Similar to MOVINGSUM but with * rather than + ;)The input is a % movement in given month as per below and I need to construct a compound growth rate. I.e. in 2028 it is 0.9438 (1.3*0.6*1.1*1.1) Regards,Adam

6752
Benji
D
dariousKeen Learner
asked in Questions & Conversations
Count Blank over Count All with Dimensions

Hi Pigment Community, I am trying to create a KPI that tracks the ratio of blank cells to filled cells for a column in a transaction list. In addition I want this KPI to be dimensioned so that it can be affected by the page filters, however I can’t seem to figure out a solution and was wondering if any of you had a solution for me. So far I have attempted 4 different solutions, however none of them have given me exactly what I am looking for. Below I have provided screenshots of the different parts that contribute to my problem. As I have tried different solutions and none of them have worked I have provided information for each formula, as I don’t understand why each one gives me a different output. My end goal is to have the proper ratio of (all blanks) / (all blank + nonblank) which can be filtered by dimensions using the page option. Specifically: year, deal status, and HB - Deal Owner. Extra info:All deals - total row count = 5,504All deals.segment = blank = 292Ratio = 292 / 5504

3151
Issam Moalla
Employee
D
dariousKeen Learner
asked in Questions & Conversations
Year-Over-Year Moving Average Calculation

Hi Pigment Community!I am trying to create a year over year moving average but can’t seem to figure it out. I was wondering if any of you had some suggestions for me. The issue I am trying to solve is that I have a conversion rate calculated in a metric. Using that metric I would like to calculate the difference in moving average between the current year and previous year. Formulaically it would be: (Moving Average Current Year) - (Moving Average Previous Year) * 100. In addition I want to incorporate my switchover date so that my metric will continuously update itself as new information comes in.  Currently I have this formula in mind, but it doesn’t work. Perhaps there’s a better way, or my formula could be tweaked. MOVINGAVERAGE('[ % ] Opportunity to Customer', 3) - MOVINGAVERAGE('[ % ] Opportunity to Customer', 3, 0, '[ % ] Opportunity to Customer'.DATE(YEAR(EDATE('Switchover date', -12)), MONTH(EDATE('Switchover date', -12)), 1)Opportunity to Customer - MetricWhat I envision is: (

7082
D
P
Newly Minted Pigmenteer
ParaNewly Minted Pigmenteer
asked in Questions & Conversations
Newbie question

Hi, I am very new in Pigment and try to get used to the multidimensional thinking.I have a transaction list containing employee and department and a calculated salary metric with monthly salary per employee. I want to group the salaries per department. Please assist

5255
Benoit
Community Manager
D
Newly Minted Pigmenteer
Daniel CicakNewly Minted Pigmenteer
asked in Questions & Conversations
Customer Rank globally & regional in one Board Widget?

Hi Community,is it possible to have a monthly ARR ranking metric which combines globally & regional rankings? e.g.Page Filter “None/All”, globally,Page Filter “EMEA”, ranking within “EMEA”,Page Filter “AMERICAS”, ranking within “AMERICAS”,etc.I was able to create a global & a regional view but it would be great to have this combined for our Dashboard, so that we can toggle through the regions. Thanks & BR,Daniel

4473
D
Newly Minted Pigmenteer
E
Just Arrived
EmiliaJust Arrived
asked in Questions & Conversations
Access to a Metric

Hello,I granted everyone admin rights to see and write in the application, however there is one specific metric, which no one else besides me can see. Did you maybe have a similar case or know what the issue might be?Thanks!

3651
Benoit
Community Manager
CamilleMomo
Seasoned Pigmenteer
CamilleMomoSeasoned Pigmenteer
asked in Questions & Conversations
Impossible to change color on open text ?

It seems that it is not possible to change the color of an “open” text widget. When changing color it goes back to “filled”.Is it a bug or should I create an Idea ?Thanks!

871
Benoit
Community Manager
D
Seasoned Pigmenteer
DastanSeasoned Pigmenteer
asked in Questions & Conversations
Selecting the 2nd Most Recent value for the Transaction list

When I want to get the most recent date of the sale of a particular product, I can use BY FIRSTNONBLANK aggregator to get the value from the Transaction list of product sales data into the metric.But, how do I select the 2nd most recent date?For instance, I have the following sales table:Product         Qty.        DateProduct A.      30.        5/12/2023Product A.      40         3/26/2023Product A.      15.        1/11/2023I would like the metric, which I’m creating, select the 2nd row’s date, i.e.:Product A.   3/26/2023

2767
BrunoRodriguez
Employee
A
Just Arrived
azianiJust Arrived
asked in Questions & Conversations
XIRR function does not return value

Hello, I am using this formula to calculate the IRR amount, but it does not return anything. Could anyone help please ? Thanks in advance.IF(CoC<1,XIRR(Amount[ADD: Company][filter: LEFT('Transaction ID'.Name,8) = Company.Company][BY SUM:'Transaction ID'],-0.2,FALSE,Day),XIRR(Amount[ADD: Company][filter: LEFT('Transaction ID'.Name,8) = Company.Company][BY SUM:'Transaction ID'],0.1,FALSE,Day))  

4052
Benoit
Community Manager
Guillermo Puls
Budding Pigmenteer
Guillermo PulsBudding Pigmenteer
asked in Questions & Conversations
Count if on Text metrics

Hi folks,The following query is about generating a Metric with data type dimension “Employee”The metric is counting how many items of a Text metric 1 are in another Text Metric 2.I use the following formula:If ( Text Metric 1 = Text Metric 2, “Employee”) [REMOVE Lastnonblank: Month, 'Employee']My problem is that I have no results when I should have. Thanks in advance!! New Metric - Type Dimension (Employee) Text Metric 1 Text Metric 2 Cost Center Cost Center   Entity Entity   TBH Request TBH Request   Version Version Version     Month     Employee

5022
Benoit
Community Manager
M
Seasoned Pigmenteer
mmusabSeasoned Pigmenteer
asked in Questions & Conversations
Access Rights Without User Dimension

Hi Team I have a use case where i have certain questions as a dimension and there are in total 160 of them. We ask each property manager these question but for lets say Property A , we ask 50 questions and for property B we ask all 160 of them.  How do i apply access right so only relevant questions get assigned to each of the properties? It doesnt need user access , just need to define it by property itself and questions. Secondly, then i need to create a metric to track progress rate as to how many of these questions are still blank and for that as well, i need to divide it by only the applicable questions. So for example property A have 50 questions to answer and he have only done 5 so far, then my progress rate should be 5/50 i.e 10% not 5/160 which is taking the whole 160 questions in the original list of questions. ThanksMusab

1711
Nathan
Employee
C
Budding Pigmenteer
connorsmithBudding Pigmenteer
asked in Questions & Conversations
Linear Forecast: Selecting Ranking Dimensions

Hi there! On the topic of the FORECAST_LINEAR formula: Is there a way to select certain values for the ranking dimension? For example, if:Forecasted Sales =FORECAST_LINEAR('Sales','Month’) Is there way to choose certain months you want to use for the regression? In my case I am trying to using TTM (which I currently have as a Boolean property of Month)

1944
C
Budding Pigmenteer
W
wyunngOccasional Observer
asked in Questions & Conversations
Many-to-Many Index Match Based on Dual Properties in Transaction Lists

How can you adapt the index match function in a transaction list to handle a many-to-many relationship, where a combination of 'Region' and 'Department' might correspond to multiple 'Project Codes' and vice versa?For example, both Region '1' and Department 'A' could be linked to multiple Project Codes, and a single Project Code could be applicable to various combinations of Regions and Departments. What techniques or formulas can we use to index match on these two properties simultaneously while managing such a many-to-many relationship?

2411
Benoit
Community Manager
C
Seasoned Pigmenteer
Cedric LJSeasoned Pigmenteer
asked in Questions & Conversations
Dimension property value by version

Hello,I created the below metric but the result is not what i expect.I would like to have only the “new deals. TTR first date” for the “Budget 2024” version.Currently my metric is displaying the “new deals” dimension list of all my version (Reforecast july 2023 and Budget 2024). Version is a property of New deals.You will find below the new deals dimension list.thanks for your help 

1617
C
Seasoned Pigmenteer
L
Newly Minted Pigmenteer
Lester ChowNewly Minted Pigmenteer
asked in Questions & Conversations
Scenarios for Transaction Lists

How do you guys lock headcount for a 3+9 or a 6+6 for example? Our headcount comes as an import from our HRIS load. But since we can’t apply scenarios to transaction lists, our locked 3+9 contains items from the current day.

2124
L
Newly Minted Pigmenteer
V
VianneyOccasional Observer
asked in Questions & Conversations
function "Today" in Pigment

Hello Pigment community, I am having trouble creating a metrics to compute my ageing balance. The aim is to create a metric that would calculate the delay in days between the due date of each invoice and the current day (today). I tried to find workaround on this in the community but i can’t make them work in my case. I saw as well that a replica for Pigment of the excel function TODAY() was under development but i did not see if that was live or not. Can you help me ?  Thanks a lot 

4661
BrunoRodriguez
Employee

Badge winners

  • Read and Interpret Formulas Certified
    Ghaythhas earned the badge Read and Interpret Formulas Certified
  • Read and Interpret Formulas Certified
    DiyaChatterjeehas earned the badge Read and Interpret Formulas Certified
  • Innovator
    Stefhas earned the badge Innovator
  • Read and Interpret Formulas Certified
    kimlopo23has earned the badge Read and Interpret Formulas Certified
  • Innovator
    Naomi Vriendhas earned the badge Innovator
Show all badges
Terms & ConditionsCookie settings