Questions & Conversations

I received a question to our support desk from a user recently, and I thought that it would be helpful to others to share my approach!The user wanted to extract some clean items from a text property in their transaction list so that they could find the corresponding ‘Country’ dimension items by using the ITEM() function. Generally, the values were simply an exact match to the unique ‘Name’ property for the items that they wanted to map to. However, in their source data, some items were followed by a whitespace and some additional information contained within parentheses, i.e. “Country (City Name)”.The formula that they were using was not returning results for the transactions where the source text property contained parentheses. It looked something like this:ITEM( IF( ISBLANK(LEFT(SourceTransactionList.CountryText, FIND(" (", SourceTransactionList.CountryText))), SourceTransactionList.CountryText, LE

Hi there, I’m facing a recurrent problem and can’t seem to have a solution to fix it. My problem is the following : I monitor my P&L statement in Pigment, this P&L is directly based on my general ledger. The general ledger is imported once a month, and includes all the months of the year, although the entries for previous months only change marginally. Each month, the general ledger is mapped, with a supplier column (which does not exist in the original import); a formula in Pigment is used to map according to the wording of the entry. However, sometimes the formula does not recognise the supplier correctly, or does not map it. As a result, I end up doing manual mapping to correct inconsistencies.  However, when I re-import the general ledger the following month, the manual adjustments are overwritten and I end up with unmapped suppliers. How can I ensure that they aren’t overwritten ?  How can I keep track of manual adjustments?  

Hi,We have a table, as below, used for assigning departments to employees. Additionally, we have an ARM_department metric for managing users’ department access right.I want users to see only the departments to which they have access in the dropdown list (rather than all departments) to avoid mistakes. I know that something can be set for Department dimension, but this would affect the entire workspace, right? Is there a way to restrict it only in this table?Thanks for any advice,Weining

Hello Team, I am facing a challenge with the Cardinality. Situation - I have created a metric (Say A)from a transaction list from which I am pulling a target hire date and I am dimentionlized it with 4 dimensions (month, seat, employee and position worker type). Now I am creating another metric where in i am using ISBLANK () funtion. ISBLANK (A). This is giving me cardinality issue. I think the problem is that although my data set only starts from FY 23, since my calander is set from FY19, this is giving TRUE for all the values from the years I do not want as well which I think is resulting in this problem.I tried to filter by Month.Year but the formula stops at cardinality i think and not filtering the value. Any suggestions ???

As shown above, I would like to add some color conditional formatting to the access rights. How can I achieve that? I have tried using the condition “Does not contain” with the value “Read / Write,” but it doesn't seem to work.

I am trying to create a logic to extract part of a text. For example, here:“41111 Revenue : Subscription : Platform”I want to transform the above text as: “41111 - Platform”. The issue is, when I use FIND function to find “:” symbol, it only returns the position if the first occurrence. But, I want to return the position of the first occurrence from the end. I could use nested IFs to find the first occurrence, slice the text, and find first occurrence from the sliced text. However, some accounts have 3 and more “:” symbols:51111 Cost of Revenue : Subscription : Allocation In - Subscription : Allocation In - Sales & MarketingIs there a way to locate the last occurrence of the text, instead of the first?

When mapping to address mismatched dimensions between source and target is there a best practice to use a list property or metric? Seems to me the list property is the better choice since it’s the obvious place to look which prevents creating duplicates, which of course, can easily become out of sync. If list property is the way to go are there any downsides? Thoughts?

I have created a metric. Based on current date it will fetch the current month. I have another metric for defining Historical period. I am trying to drive this by above grid.  However I am getting an error while applying the formula.   What is that I am missing or doing wrong. Please suggest the right logic

The update history is a fantastic feature. Recently my team had asked me to find who changed something and it was very cumbersome having to comb through the block when I’m looking for changes that ultimately impacted just one GL account. Is there anything on the pipeline that would allow us to search by block by also by GL? 

I have various forecasts and weights that are derived in different models and need to piece together a daily forecast.  Long story short, what I have below is the daily forecast but is set up by the date the week starts and the day of the week.  I need to “convert” that all into one metric with just the day dimension.I have created another metric that has the same dimensions but the result is itself the day dimension.And so what I want to do is somehow assign the values in the first metric to the day dimension in the second metric.  The block is fully aligned but I am not sure how to code this properly.   I’ve played around with the BY modifier but I just can’t seem to get this working.

Hello - I’m looking to add a previous function to the below equation“if('Is Forecast?',('Go Forward Cohort'[select:'Go Forward LTV Driver'."Borrower Retention"][BY:->'Map to Cohort Month']),(('2.002 Cohort View - Borrower Retention Lookup')))”I believe this formula works similar to excel where the data is either coming from the ‘Go Forward Cohort Metric’ or the ‘Cohort View Metric’ depending on the ‘Is Forecast’ boolean. I want the previous function to pull the last row of data thats a result of the if statement. Thanks in advance

Hi Team,We have a requirement of locking our Versions for any more inputs. Do we have Version Locking capability or Cellular Level Access restriction capability in Pigment?Any leads will be appreciated.Thanks,Nipsa

I want same feature like Today() in Excel.

Hi. I’m new to Pigment so hoping someone has solved this problem before.Has anyone been able to parse out the rightmost X number of characters that appear after any number of colons in a dimension member? e.g. [60001 Personnel Costs : XYZ : ABC: Salaries and Wages] should return [60001 - Salaries and Wages], where XYZ and ABC are illustrative of potential sub-accounts that need to be removed.

Hi , I am looking to figure out a solution for BOM Management in Pigment. Background:I have 2 columns in source data, one for parent and one for children.I want to create a hierachy using these 2 columns. The catch here is, parent can be a children or children can be a parent. Let me know if anyone is interested to work on this challenge with me. 

I have created a group and have added members with access to a handful of applications.  For the time being, I have assigned them the reader role.  However, I can not get them to see the boards.  The screenshot below is from me impersonating one of the users.  I’m stuck on what to do next.  Do I need to go through and change permissions to each of the boards?  Or even each of the blocks?  Is there a quick way to have them see the boards?  Do I need to edit the reader role and change permissions there?  I have read through several articles and I can’t match the instructions to my workspace.  Any help or insights would be much appreciated.Kevin 

Hello! The SELECT modifier can be used to Offset a dimension (ex. SELECT: Month - 1), but is it possible to assign different Offset amounts based on one of a dimension of the Metric? Here is the example I am stuck at now: I am trying to assign specific Offset amounts to a metric (0. UFR - Live) based on the Customer Payment Term dimension (ex. For any data in the Net 30 → 1 month offset, Net 60 → 2 month offset, etc) Ideally I could assign an Offset Integer as a Property of the Customer Payment Term dimension and that would determine the Offset (ex. [SELECT: Month - ‘Customer Payment Term’.’Offset Integer’]Is there a better and more sustainable approach rather than a massive nested IF statement? Thank you! 

Could anyone provide some guidance on when to use the function SEASONAL_LINEAR_REGRESSION over FORECAST_ETS? In our specific case we have a metric with a yearly seasonality and the overall trend for the metric is going slightly downwards. We would like to use our actuals data to automatically calculate values for all forecast months. I can see that both functions gives me reasonable values for my forecast, but FORECAST_ETS gives me a clearer trend downwards while SEASONAL_LINEAR_REGRESSION gives me more flat forecast. So my question is really, which one would make most sense to use in our case and why?

Hi all,Curious to see how people have approached (1) creating an application guide for new users (specifically something to quick start leaders that don’t necessarily have the time to go through more in-depth tutorials), and (2) board/block structure. I know there’s some documentation on board/block structure, but just curious to get some more context on how people approach it, particularly for revenue focused planning. Thanks! 

Hello everyone,Is there a feature in Pigment similar to the Solver in Excel that involves solving equations based on many inputs and conditions to maximize or minimize an objective?Regards,

Hello! I am seeking to make 1 Table that has 2 metrics (image below). The metrics are “P&L” and “Commentary”.For the P&L metric I am seeking to show two different scenarios (“Actuals” and “Q2 Outlook”)For the Commentary metric I am seeking to show ONLY 1 scenario. Is there any way to achieve this? It seems like the Scenario page selectors apply to ALL metrics in a table.Curious if others have found get arounds for this? 

Hi all - we are running into an issue with our Google Sheets connector where the order in which scenarios are selected in the saved view does not seem to get passed on to g-sheets. Explaining this in more detail below: We have added a screenshot below showing our list of scenarios. We want “Apr 24 Close / May 24 Reforecast” to be the first scenario and “FY24 Company Plan (Approved Changes)” to be the second scenario -- this is because we want Apr close to be on the left and FY24 company plan to be on the right hand side of our table. When we export this view using the Google Sheets connector, we are required to select the 2 scenarios again and their order seems to be reversed once we pull the data (see second screenshot below).  Any thoughts on whether we’re doing something wrong or if there are any workarounds available for this?

Hello - I am trying to update a formula based on a boolean that is determined by the latest date in a data upload. I believe the following equation is close to what I need.Formula: “if('Is Forecast?','Go Forward Cohort'[select:'Go Forward LTV Driver'."Borrower Retention"][BY:->'Map to Cohort Month'] , if('Is Actual?',('2.002 Cohort View - Borrower Retention Lookup')[remove lastnonblank: Month][remove lastnonblank: Cohort][ADD: Cohort]))”There are two issues:1. I want to add a previous function to the first part of the if statement. If there is no data to pull from the “go forward cohort metric” I want the function to pull the last data point there is2. When updating to the above formula my application will time out. Is there a more efficient formula that will prevent this? Thank you!

Hi, Is it possible to create 3 distinct views on 3 distinct metrics, and combine the metrics into 1 table that incorporates the 3 distinct views? Alternatively, say I have a table constructed from multiple rows that are each a metric. It would contain 3 sections (Revenue, COGS, and OPEX). Is there a way to have unique row pivot selections for each of the 3 sections and not having the row pivot selections impact all rows instead? Or will this require 3 unique tables, separating the 3 sections? 

Hi all, I am trying to export data in a given metric to azure data factory. I am following the guide in the pigment documentation exactly, however when i go to pull the data, I get a 404 error. I have set up the export keys and I am using the correct applicationID and the correct metricID. Any insights into what else I may be missing would be great.Thank you