Skip to main content

Questions & Conversations

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

  • 799 Topics
  • 2,163 Replies
799 Topics
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,  

31410
M
Budding Pigmenteer
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.

1041
Issam Moalla
Employee
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?  

1982
Thu Mai
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.

1371
Thu Mai
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

692
Thu Mai
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

1465
A
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!

3312
Issam Moalla
Employee
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 

1522
Issam Moalla
Employee
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

1973
E
Seasoned Pigmenteer
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.

2751
CamilleMomo
Seasoned Pigmenteer
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

3342
Cat M
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. 

4794
M
Seasoned Pigmenteer
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

43611
E
Seasoned Pigmenteer
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! 

2751
francois
Employee
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!

3733
Y
Newly Minted Pigmenteer
P
Newly Minted Pigmenteer
PaylwardNewly Minted Pigmenteer
asked in Questions & Conversations
Demo Ideas

Hello, I have gone through all of the available training and videos for Pigment modules and setting up Demos. I would like to know if there are any additional Demo resources and data I can use to test my skills.

2272
KarlW
Community Manager
K
Seasoned Pigmenteer
KevinSeasoned Pigmenteer
asked in Questions & Conversations
Is it possible to use Previous() simultaneously across multiple dimensions?

I am building an attrition model based on month dimension and a dimension called ‘30 Day Cohort’.  I am almost certain that I will run into a circular reference if I do not use the Previous function.  However, because I am working with 2 dimensions I am unclear on how to reference the previous month and previous ‘30 Day Cohort’ dimension simultaneously.  Is this possible?  Can I combine multiple Previous functions?  Is it possible to nest two Previous functions? To start my model, I have “initialized” the metric with both the starting values in the first month and the forecasted new subscribers in the first ‘30 Day Cohort’ (0 in my case): What I would like to do is calculate a formula on the value in the previous month and previous cohort and then do so recursively (this is why I think I need to use the Previous function).  So for Oct 23 and cohort 1, I would want to do my calculation based on the Sep 23 and cohort 0 value (1,895); for Oct 23 and cohort 2, I would want to do my calcula

8508
B
Newly Minted Pigmenteer
C
Budding Pigmenteer
Carolina LeeBudding Pigmenteer
asked in Questions & Conversations
Balance Sheet: Net Income

Which formula can I use for Net Income at my balance sheet in the begging of the year where I need to transfer all Net income to Retained earnings / losses and start accumulate again? See below an example. I read this article below but I couldn’t understand: 

3894
P
Newly Minted Pigmenteer
M
Seasoned Pigmenteer
mmusabSeasoned Pigmenteer
asked in Questions & Conversations
Repeating set of values until the a certain condition

Hi TeamI have a calc which uses Year dimension and the year starts from 1 to 20. There is an assumption metric which defines what year the crops will be harvested. So lets say currently the assumption is year 6 in which the crops will be harvested, then from that year onward second rotation will start and then follow similar process until Year 6 where it again will be harvested and planted again.So as an example in excel, you can see from FY19 the year starts as 0 as that is where initial plantation happen. Then it counts from Year 1 to 6 and then at Year 6 which is equal to our assumption metric (Year 6) as well, it stops and then a new cycle starts from 0 to 6 all over again. How can we built something like this easy way? I have tried thinking of using MOD in which lets say in our Year dimension, we have count column as well that goes from 1 to 20 . So what i am doing is saying if Year.Count/Assumption metric =0, then mark it as harvest year.  Now how can i get it to count and reset

1771
Issam Moalla
Employee
P
Newly Minted Pigmenteer
ParapebuneNewly Minted Pigmenteer
asked in Questions & Conversations
How to create a weighted distribution

Hi!I have a sales volumes metric with dimensions Category, Channel, Product and Month.Data was imported from a transaction list.I want to adjust this with some volumes that are manually input by user per channel, but I want to spread this values on every product using the same weighted distribution per category and month as the initial data. For example, if on month Jun, on a product, the volume was 15% of the total, I want to add on that product for Jun 15% of the manually added volume.How do I achieve that?Thanks in advance!

1791
S
Newly Minted Pigmenteer
M
Newly Minted Pigmenteer
Moosa MalickNewly Minted Pigmenteer
asked in Questions & Conversations
Removing Blanks in Transactions List

Hi,Is there a way to remove blank cells in the grid of a transactions list. I am applying a filter to the transactions list which gives me blank rows which I want to get rid of. Thanks

4954
M
Newly Minted Pigmenteer
M
Newly Minted Pigmenteer
Moosa MalickNewly Minted Pigmenteer
asked in Questions & Conversations
Filtering a dimensions list based on another dimensions list.

Hi,Is it possible to filter a dimensions list based of another dimensions list e.g. if I want to just filter for Revenue, can I filter based on the classification field which comes from another dimensions list.

1221
Nathan
Employee
E
Budding Pigmenteer
ELeff_AccBudding Pigmenteer
asked in Questions & Conversations
Compile Commentary from different tables

Does there exist a way to take the commentary (a metric of type text) from across two different tables? For instance, commentary about an Opex Expense related expense, for example Travel Expense (table 1) and Employee Expense (table 2).  Each table has a different set of metrics and each allows for text entry to supplement the expense to provide additional detail.  Downstream the request is to compile all expenses in a FPA view of all expenses entered across the company and then additionally to include the expense detail (text) with the relevant expense lines.  Bringing the data from the two different tables is no issue as we can use the “+” operand to include data from both tables.  This presents an issue when attempting the same with Text as the metric type of text does not accept Text as an arithmetic operand.  I attempted to utilize & but that did not align properly to the relevant line items being planned.  Curious if anyone else has come across this...

2023
E
Budding Pigmenteer
D
Seasoned Pigmenteer
DastanSeasoned Pigmenteer
asked in Questions & Conversations
Dynamically Aggregate items in Chart views

 Hi Pigment friends, I wonder if there is a way to dynamically aggregate calculated values when using multiple items in a dimension? I am sure my question is confusing, so hence, I will give an example.We are presenting Average Contract Value (ACV) in each segment throughout the year on a monthly basis (we have 3 segments):Each node in the chart is the ACV and all segments are currently selected. The thing is, when we want to represent aggregated ACV with all segments, we need to create a separate chart: The downside of this chart is that we cannot dynamically add/combine/remove segments, because it will start summing them up, instead of showing ACV of combined segments. For instance, if I select Small customers ACV ($500) and Medium customers ACV ($1000), and we have 10 small customers and 5 medium customers, the average aggregated ACV should be: (10 * $500 + 5 * $1000) / (10 + 5) = $667. With aggregated chart, it will sum $500 and $1000 and show $1500 as total ACV, if Small and Mediu

3482
D
Seasoned Pigmenteer
P
Newly Minted Pigmenteer
ParapebuneNewly Minted Pigmenteer
asked in Questions & Conversations
Manual Input Board

Hi! I have a numeric metric of volumes of selling items with dimensions: item, channel, category, monthFor this metric I have some volumes imported from a transaction list. Apart of these volumes I want to use an input board which allows me to manually input extra volumes per item/channel/category/month which will finally add to the imported volumes.In the board, the input widget should show all the items that were manually input and should have the option to input a new volume based on the dimension selection.How do I  do that?Thanks in advance!

3835
Issam Moalla
Employee

Badge winners

  • Read and Interpret Formulas Certified
    FelipeFonsecahas earned the badge Read and Interpret Formulas Certified
  • Pigment Like a Pro
    meghrajhas earned the badge Pigment Like a Pro
  • Pigment Modeling Fundamentals Certified
    meghrajhas earned the badge Pigment Modeling Fundamentals Certified
  • Pigment Modeling Fundamentals Certified
    JamesMhas earned the badge Pigment Modeling Fundamentals Certified
  • Pigment Like a Pro
    JamesMhas earned the badge Pigment Like a Pro
Show all badges
Terms & ConditionsCookie settings