Questions & Conversations

Hello community, I’m creating a transaction list to be inputted manually by an end user.How can I have a reduce list for a certain dimension?eg: in a country dimension with all countries, the list available needs to be only the European countries. As a workaround I’m creating extra lists to have these sub-lists. But it will not be a very clean strategy in the end. Does anyone have another way to overcome this?Thank youSofia

I have a dimension that is a number (30 Day Cohort).  I think I will need an identity matrix for some calculations later in my model.  Is it possible to create a metric that has the 30 Day Cohort x 30 Day Cohort?  Here is a simple example of what it would theoretically look like.I have tried playing around with the Value function to convert to numbers and do some sort of comparison to the dimension but it doesn’t return what I would like:IF(VALUE('30 Day Cohort'.Name)<VALUE('30 Day Cohort'.Name),0,1)[BY SUM: '30 Day Cohort']Any ideas or things to try would be much appreciated.  At the end of the day, the 1s in the matrix will eventually be values calculated from other metrics

I have a metric that has two dimensions that have pasted values from another source.  What I would like to do is return the last month that there is a value > 0 for a specific row in the metric.  In Excel terms, this would be done with a MAXIF function where I would return the max month with the condition that “Net Installs” is > 0.  From the screenshot below I want to return the month dimension value of Dec 24Can this be accomplished using just the BY modifier and the MAX aggregation method?  Any insights are very much appreciated.  Thank you in advance.   

Hello, i would like to do the sum or the average of last 12 months before the switchover month. Thanks for your help

Hey team, I have an issue to chose the proper modifiers in one of the metric blocks. I have a quota values by month by sales rep and wanted to sum these values into the quarters as a first step. So I’m writing a formula= Metric block with quotas by month [by sum: Month.Quarter]Next step is to use in quarter seasonality which is by Month, see the screenshot below. Total for a quarter is 100%. What’s the syntax to spread quarterly quota value to months ( I was testing add constant but it didn’t work) and then use the in quarter seasonality?  

Hello,  Can you please help me creating a formula to show net working days for each month?The excel formula will be like this: So far, I created a dimension  I referenced the article here but could not really understand what I should do to get list of working days for each month.  Thank you!

Hi,What’s the criteria to have a another level of breakdown?I was able to have two levels of break down for one of the metric but for the boxed one “R&D SAAS Total” another level of break down doesn’t show up even though the  dimensions are all different for each level that I wanted to select. Can anyone share what is the rule/condition to be followed for such multiple level of break down in a table?Thank you! 

Hello, can you please help me with this formula ?I would like to do a formula similar to vlookup between Unique item values (text format) of two transaction lists (check if a transaction list contains (or not) the Unique item values of the other transaction list → if Unique ID of TL1 is in TL2 then yes, otherwise no) Is it possible to use vlookup equivalent between text format values, or do I have to create a dimension ? Thanks in advance!

Hello here!I hope that you are well.Please, is there a way to evaluate the size of an application (like Mb for excel) or the number of calculations made in a Metrics? Sometimes, the calculations take time and I would like to know from where I can remove complexity/heaviness. Many thanks

Hi Team I need to compute cell by cell XIRR . So for example, first IRR will be calculated from FY21 to FY 22, FY21 to FY 23 . them FY21 to FY24 and goes on and on. Is there a way to achieve this?  So its an expanding calc starting with FY21 and keeps on expanding by brining in additional year. ThanksMusab 

Hey Team, I have a transaction list with all employees in the Workforce planning and in the same application we created the Employee ID with various properties mapped to this dimension like country, department etc. I would say classic Employee ID dimension block with a mapping. Now we have a need to add additional dimension property at the Employee Id level for a respective department which can be added manually employee by employee by the other team who makes the allocation of sales forces in the other application i.e. it’s not a Workforce app. What type of block we should create to have a kind of a table where we have a list of employees and then column with the list of properties for the allocation?PS. We don’t want to do it at the transaction list level due to security and also this infotramtion will never be available in our HR system to import it. Thanks! 

Hi Pigment team!  Is there a formula to look for the names dimension name in the Memo, and populate the EE dimension in the new property? Thanks in advance!!   

Hi All, I am using MOVINGSUM which is very helpful but I stumbled on a challenge. I need to use a metrics as WINDOW SIZE rather than a fixed number. Basically the window size need to change dependently on month. I am getting the following error:Error: Function MOVINGSUM requires second argument to be a scalar integer but is defined on Dimensions '00 Hub'::MonthAm I doing sth wrongly? Is there a workaround?Regards,Adam

Hi, all, I am currently working on a 3 segment cashflow model within pigment. We are currently trying to model the operating cash flow but are stuck with an issue regarding a circular dependency.Within the model, we calculate cash flows (investing, operating and financing). The operating cash flows use management fees to calculate the tax paid, however the management fees are based on total assets which include cash and cash equivalents, which in turn is based on the net cash flow (derived from the combination of investing, operating and financing cash flows). This ends up with a circular reference. Our excel document avoids the circular reference by utilizing the net cash flow from the previous quarter, however doing this in pigment ends with the same circular reference  

I have a transaction list with a date as a dimension.  This is done so that I can leverage the “delete existing items / limited scope” import functionality as the source data is purged after a 3 year rolling window.  Because the date has to be a dimension to do this, I am having difficulty creating the most basic functions on handling the date in my import.  It seems that every date function gives me an error that an argument must be of type date.  Is there a function I can use to convert the dimension into a date type (i.e., a nested function) so I can use the date functions?  I realize I could just change the field in the transaction list to date but I really want to leverage the limited scope functionality.  Any insights would be much appreciated. Kevin  

Dear community, I hope you are all well.Concerning the new pigment update of sheet view.How is it possible to do an XLOOKUP from another sheet? Thank you

I currently have an Employee Dimension, consisting of four columns:Employee ID | Office | Contract Start Date | Contract End DateI would like to use this to calculate the number of employees in each month, grouped by different offices, as shown in the following figure. The pseudocode would be something like: Count(Employee.EmployeeID, Date.Month > Employee.ContractStartDate && Date.Month < Employee.ContractEndDate)Do you know how to implement this calculation?Thank you in advance!

Hi Everyone Is there a way to bring in sheet’s view calculations that we do based on existing metrics and use them in to create a metric?  ThanksMusab

Hi Community: The problem: I have 2 transaction list with identical fields. I want to join them together in one single transaction list.How to do it?

I’d like to create a metric dimensionalized by Month with a Number data format. I want each cell to contain (1) If actuals, the number of days in that month (2) if the current month, the number of days from the start of the month to the current day (3) if forecast, zero. Can anyone please advise on how to do this? I know that I will probably need an input cell with the current date since there is no equivalent of the excel TODAY function. I tried using the ‘DAYSINPERIOD’ formula but couldn't get Pigment to accept the formula / deliver a result.

I have a dimension that is over 100,000 items.  I am able to import all the items from a csv file.  However, once loaded, I can only view the first 100,000 items.  There is a menu at the bottom of the dimension list that makes it seem like I can view the next 100,000 items but it does not do anything, and I cannot view the next items past 100,000.  I want to delete the very last item, but I cannot access it.Not sure if it is related, but I have the same issue for transaction lists that are over 100,000.  I can only view the first 100,000 items.  This is problematic if I would like to edit the list or manually add an item.

Bonjour,Nouvelle application et nouvelle métric à créer. Je suis bloquée pour faire un filtre sur une semaine. J’ai une base de donnée où j’ai les commandes avec la semaine de passation de la commande. J’aimerai donc faire un filtre pour n’avoir que les commandes qui ont été passées avant la semaine 35 par exemple.  Comment puis-je faire ?  Merci par avance pour vos astuces ^^ 

Hello dear Pigment Community! 🖐Is there a Pigmenteer who’d be available to connect with another Pigmenteer to chat about the experience of doing their revops model in Pigment? 👀👂🤝An informal chat to connect with another fellow peer and share your expertise!💡Don’t hesitate to tag people you think could be up for this ! 🙌

Here’s our table for quarterly metrics. Once we close another quarter, we need to change the pages and adjust QoQ calculated items (even though they may be based on variables). What is the best way to select pages dynamically? For example, trailing 5 quarters?

Hello guys, I’m having a problem when trying to import this CSV file and getting this message, yet I have not used “Buyer TIN” dimension as the unique property, I used another different item as the unique property. What can I do..?