Skip to main content

Questions & Conversations

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

  • 827 Topics
  • 2,224 Replies
827 Topics
Bas
Budding Pigmenteer
BasBudding Pigmenteer
asked in Questions & Conversations
P&L view in % of net sales

Hi everybody,I'm running into some difficulty when trying to present a P&L in % of net sales at subtotal levels.I have a ragged P&L hierarchy (account dimensions L1 to L8, where my net sales is defined at L3). I want to show all values, including the subtotals, as a % of the specific item “net sales” at the L3 dimension.When I try to use ‘show values as’ as a % of another metric where all my lowest level accounts contain the total net sales amount, it works for the lowest level (L1) accounts but the subtotals are aggregated (that is: the total net sales amounts that are allocated at the lowest account level (L1) are incorrectly aggregated together, which throws off the %-calculation). For example: I have 10 L1 accounts, each of which contains 100m as net sales. At the subtotal L3 (net sales) level, I then get 100m / 10x100m = 10%, where I would expect to see 100%. I also tried to use ‘show values as’ as % of a specific item of the L1 dimension, where I used an item variable tha

112
Stef
Employee
G
Newly Minted Pigmenteer
geoff_chovazNewly Minted Pigmenteer
asked in Questions & Conversations
SFTP connection

I am receiving an error “xxxxxxxxxxxxxx is not a valid unix username” when trying to establish an SFTP connection. The username is quite long - 27 characters. The connection works in other SFTP clients like Filezilla using the exact same parameters. Any ideas? 

60
G
Newly Minted Pigmenteer
P
pigment_enthusiastKeen Learner
asked in Questions & Conversations
Data Action - Trigger Export - Not exporting Blank rows

I have a table with 2 metrics: M1 and M2. Both metrics M1 and M2 are having the Customer dimensionality.Created a data action to export the table,Action on click: Trigger ExportSelected the table/block name and then 2 metrics in the dialog box in ‘Action Widget Settings’, header names is kept as “Display Name”.Noticed, while getting the export i.e. CSV file in the downloads folder, it contains only those rows which are having values either in M1 or in M2, but I want to see all the customers in the export view even if M1 and M2 both are Blank.

191
M
Employee
G
Just Arrived
ghitaJust Arrived
asked in Questions & Conversations
dropdown dependency

 I have created a dropdown dependency, but the issue I am facing is that I want to apply it to 3 or 4 columns from the same source (same dimensions list ). However, I cannot link these 4 columns to the same logical object.

141
CDALMAY
J
Newly Minted Pigmenteer
JB BuffetNewly Minted Pigmenteer
asked in Questions & Conversations
Countuniqueof with several dimension

Hello,I need to count the number of client inside a Database by several Dimension (Month, Business Unit, Category(New, Upsell, downsell, Churn), Client type, ...)I tried using COUNTUNIQUEOF but it doesn’t work (timeout of request).IF(Month < 'Switchover Date for Forecast/Budget',IF('Full data BI'.subscription_type = 'Subscription Type'."Renewable" AND 'Full data BI'.segment_revenue_category <> 'Customer Category'."Churn",COUNTUNIQUEOF('Full data BI'.customerid) [by :'Full data BI'.segment, 'Full data BI'.segment_revenue_category,'Full data BI'.Month, 'Full data BI'.customer_type, 'Full data BI'.product, 'Full data BI'.product_type, 'Full data BI'.region, 'Full data BI'.subscription_type]))Could you please help me sort that out ?

213
Benoit
Community Manager
A
Newly Minted Pigmenteer
andyzillaNewly Minted Pigmenteer
asked in Questions & Conversations
Dimension property types - converting to other types and changing dimension lists

Hi Pigment Community,I have a couple of questions about managing properties of type dimension.I’m using a direct connector to Lever to create a Lever_Requisitions transaction list. I need to use one of the properties of this list (Requisition ID) both as a dimension and as text - I’m using it as text as part of a concatenation for an Unique ID property that defines how my loads work and now need to use it as dimension to cross some data with an offer transaction list. Is it possible to have the same data in two different columns with two different types? How can this be achieved? Additionally, I need to change the dimension list associated with my Requisition IDs, as it was automatically generated from my offers report and is incomplete. How can I do so without losing all items in my existing Lever_Requisitions.’Requisition ID’ property? I have historical loads there that I wouldn’t like to lose. Thank you!

172
M
Employee
W
Newly Minted Pigmenteer
williamwestmorelandNewly Minted Pigmenteer
asked in Questions & Conversations
How to count items in a metric without removing dimensionality?

Howdy y’all - Our team is working on doing a simple customer count, but is struggling to do so without removing all dimensionality from our metric.We want to count unique instances of a customer where there is spend - but have dimensionalities flagging different types of spend attributes (ex: a flag for customers who have spend in the US vs those who have spend outside of the US). So - if a customer has spend in both the US and outside of the US - they will have a customer count of over 1, when we want each customer to only be counted once while retaining dimensionality. None of the count/countunique formulas are adequately solving this issue.The page aggregators are correctly counting the number of customers - but we are unable to use the page aggregators in tables to do calculations. Does anyone know of a way to use the “count” from the aggregator for table calculations - or of any other ways to get customer count without double counting across different dimensions?

717
Benoit
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

8859
Y
Newly Minted Pigmenteer
alexkonrad
Budding Pigmenteer
alexkonradBudding Pigmenteer
asked in Questions & Conversations
Automatically adding aggregations after collapsing

Hi all, I have a strange behavior in some reports: if I collapse a dimension it adds an aggregation.For example I have a report which shows the following list of dimension and only the Months is aggregated inthe columns.In my example the Planning Cycle dimension is not aggregated (which wouldn’t make sense).But if I click on [- FY] to collapse the last columns it automatically activates the Planning Cycle dimension aggregation.The filter is activated automatically: I would like to deactivate this behavior.Best,Alex

132
alexkonrad
Budding Pigmenteer
P
pigment_enthusiastKeen Learner
asked in Questions & Conversations
How to import the data from another app without allowing the users to drill down

Use Case:There is App1 and App 2 within the same workspace. Need to bring the metric’s Xyz values with Employee dimension from App 1 to App2 on a real-time basis, i.e. in App 2, the user shouldn’t be able to drill-down on the underlying logic written in  Xyz metric.Created the scheduler import (metric to metric import) in App 2 to fetch the value from App1 and fetched it in a PQR metric in App2 , and then created another Final PQR metric: which is just referencing ‘PQR’ metric and published Final PQR on the board, but need to bring the data real-time , how to do in Pigment- Can anyone please guide me with this?

121
alexkonrad
Budding Pigmenteer
P
pigment_enthusiastKeen Learner
asked in Questions & Conversations
Submit Button

Is there a way in Pigment to submit a button?(i.e. similar to marking a checkbox as True)

181
F
Employee
Y
Newly Minted Pigmenteer
YaejisNewly Minted Pigmenteer
asked in Questions & Conversations
Current Forecast Week

Hi!  Can you please help setting up the formula to populate the current forecast week?I already have a Week dimension and the metric should populate 2025-06-08 as of today because 2025-06-01 is marked as “Last Actual Week”.  Thanks!

191
Min Li Bright Point
J
Just Arrived
JessicaJust Arrived
asked in Questions & Conversations
Map dimensions based on text identifier

Objective:Automatically map list of transactions to a vendor based on whether the name of the Transaction contains a keyword from the Vendor dimension (specified in a mapping table), and return the matched Vendor. I have a dimension block which contains a list of transactions (incl. a text property with the transaction name & a property formatted by the vendor dimension where I map them to a vendor). I also have another dimension block called Vendor which contains a list of vendors & a text formatted property where i want to input a key word for each vendor that is typically in the transaction name. I want to leverage this mapping in the Vendor dimension and do a lookup on my transaction list to check each transaction name. search if it contains a keyword and populate the correct vendor. Is this possible?

431
F
Employee
N
Budding Pigmenteer
Naomi VriendBudding Pigmenteer
asked in Questions & Conversations
Transforming a data type Boolean into a Dimension

I have a metric where I have two dimensionsParent Company Sector CategoryFor each company, we assigned one sector via Boolean. For example in the image, beflex electronic GmbH is “Mechanical Engineering”I want to create another metric with the data type “Sector Category” and the only dimension is Parent Company, so that I can use this to map all my per Parent Company revenue, to their corresponding Sector Category, or a column in the parent company dimension to that Sector Category.How can I do this? I essentially want to move a dimension from being a dimension within the metric, to it being the return type of the metric (when the boolean is true).Thanks! 

272
N
Budding Pigmenteer
E
Newly Minted Pigmenteer
epachoNewly Minted Pigmenteer
asked in Questions & Conversations
Workday to Pigment Import Failed - Previous owner is deactivated

Hello, our Workday to Pigment failed to load as the previous member was deactivated on 5/30. Please advise on how best to proceed. Thank you in advance for your help!ent integration appears to 

202
E
Newly Minted Pigmenteer
C
Newly Minted Pigmenteer
CalvinNewly Minted Pigmenteer
asked in Questions & Conversations
Dimensioned Text Card Widget - Bulk Formatting

Is there a quick way to bulk format a dimensioned text widget across multiple dimensions? I have a metric that has 2 dimensions and I’m finding that for every intersection I have to reformat the card (ie. change the background colour).

242
C
Newly Minted Pigmenteer
Bas
Budding Pigmenteer
BasBudding Pigmenteer
asked in Questions & Conversations
Joining two dimension lists into one?

Hi everybody,I have two dimension lists: one for my P&L accounts and one for my balance sheet accounts. Both of them are updated with new accounts once they appear in the P&L transaction list or the balance sheet transaction list. For reporting purposes, I want to create a single chart of accounts dimension list that contains all the items from the P&L and balance sheet dimensions lists. Is there are way to combine both dimension lists into one and to do that in such a way that it automatically updates once a new P&L or balance sheet account appears in either of the two transaction lists? By the way; both dimension lists currently do not share the same properties. So I would simply be looking to combine the unique values/dimensions from each list and append them together as a new total chart of account list.Thanks for helping me out :)Best, Bas

644
Stef
Employee
H
Newly Minted Pigmenteer
HuyenNewly Minted Pigmenteer
asked in Questions & Conversations
Connect Pigmet with Sage100

Hello,I’m just a beginner, and currently, my company uses the Sage 100 software, with data stored in an on-premise SQL Server. We also use the Sage BI Reporting tool to query the data in Excel.My question is: how can we directly connect to the Sage accounting data?Thanks a lot!

312
H
Newly Minted Pigmenteer
K
Newly Minted Pigmenteer
Karin TauberNewly Minted Pigmenteer
asked in Questions & Conversations
Pro Training - 5. core Reporting PnL_Account 6001

Hi,as part of the fifth section of the Pro Training, I need to Allocate all values of  Workforce Cost Plan → to PnL_Account 6001. What is missing in my formula?Workforce_Cost_Plan_Output[ADD CONSTANT: PnL_Account."6001"] keep getting an error:“Error: Syntax error: cannot validate formula” Thanks in advance    

391
T
Employee
sudhakar
Budding Pigmenteer
sudhakarBudding Pigmenteer
asked in Questions & Conversations
Is there a filter during import configuration

I have source files to build dimension lists like Territory and Product with hierarchical relationships which have hierarchy level column. I want to build Territory and product dimension lists by importing the respective files and filter by lowest hierarchy level (a number that show the level) during import definition. Is it possible?For example I have a product file with below columns, I want to load Products that are equal to Hier Level ‘3’. 

522
sudhakar
Budding Pigmenteer
M
Budding Pigmenteer
MaxenceGoigBudding Pigmenteer
asked in Questions & Conversations
How to check if each active customer received an invoice this month?

Hi everyone,I’m trying to determine whether each active customer received their invoice for each month.I have two tables, both having the dimensions month and customer ID: An "Activity" table, which has one row per customer per month, indicating whether the customer was active. If the client was not active on a specific month, he will just not appear in the table for this month An "Invoices" table, which lists all the invoices sent. Thanks in advance for your help!

1036
M
Budding Pigmenteer
Y
Newly Minted Pigmenteer
yacine.ibouricheneNewly Minted Pigmenteer
asked in Questions & Conversations
From "Month" to "Year" aggregation issue

Hello Everyone ! I have created a metric which will calculate a simple EBITDA/Net Sales ratio, which is as followed:I want to be able to switch to a yearly view easily. The issue is that when I pivot the table to show yearly data, it aggregates all the months instead of displaying the ratio between yearly EBITDA and yearly Net Sales. I need it to show the correct yearly ratio, not just a sum of monthly values. Is there a way to prevent aggregation and ensure that the metric correctly calculates the yearly ratio ? I want to ensure it’s possible to switch views while keeping the same metric. Ultimately, the goal is to use this metric in a board and apply a page selector to display the desired month as a KPI..Thank you by advance for your help 🙂Yacine

343
Y
Newly Minted Pigmenteer
K
Budding Pigmenteer
keerthanarameshBudding Pigmenteer
asked in Questions & Conversations
Create dimension list from another dimension list property

I have a dimension text property with a list of items. I want this list to be populated as a new dimension list. I used a concatenate in this property to get a new text item and I want this new text to be converted into a dimension. How can I do this with the help of formula?

1182
Benoit
Community Manager
Bas
Budding Pigmenteer
BasBudding Pigmenteer
asked in Questions & Conversations
Excel connector doesn't recognize named cells

Hello,I'm experiencing some issues with pushing data to Pigment from Excel. I have a simple dimension list (800+ items with about 4 dimension properties) that I would like to periodically update from Excel. I exported the current dimension list to .csv and used that .csv to setup an import configuration in Pigment. Then I pasted the .csv in Excel and updated the 4 dimension properties. I then named the data range in Excel but when I try to configure a push connection it doesn't recognize the named cells (“no names defined”).Also, when I try to input the cell ranges manually (A1 → G800) and then push the data to the dimension block in Pigment, the import fails (headers are not found...). Any help is appreciated. Thanks!Bas

241
M
Employee
Bas
Budding Pigmenteer
BasBudding Pigmenteer
asked in Questions & Conversations
Index versus LYTD shows only blanks

Hello,I have one table that has a number of calculated items in them. In the order of events: first the YTD value is calculated using cumulate (works fine). Secondly, the index versus budget is calculated (works fine for YTD) and lastly the index versus LY is calculated (that one goes wrong for YTD (only blanks). The strange thing is, is that that ratio versus LY does work fine when calculated over the month, but it shows blanks when calculated over YTD.Below are snapshots of my calculated items. Can anybody see why the index versus LYTD would return blank values?Thanks, Bas  

181
M
Employee

Badge winners

  • Innovator
    Cat Mhas earned the badge Innovator
  • Read and Interpret Formulas Certified
    LongourAhas earned the badge Read and Interpret Formulas Certified
  • Helper
    Karin Tauberhas earned the badge Helper
  • Pigment Like a Pro
    thrihas earned the badge Pigment Like a Pro
  • Pigment Modeling Fundamentals Certified
    thrihas earned the badge Pigment Modeling Fundamentals Certified
Show all badges
Terms & ConditionsCookie settings