Skip to main content

Questions & Conversations

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

  • 627 Topics
  • 1,701 Replies
627 Topics
Ola
OlaFrequent Reader
asked in Questions & Conversations
Trigger import through API without sending data

Is it possible to trigger an import from Netsuite, Amazon S3 or Salesforce using Pigment’s API?So in this case we don’t want to send the data in the request body, we just like to trigger an existing import using the API instead of having the import to run on a schedule. Just want to confirm if this is supported or not, can’t really tell by reading the documentation here:  

1
Lehtohen
Budding Pigmenteer
16 hours ago
C
Newly Minted Pigmenteer
CindyNewly Minted Pigmenteer
asked in Questions & Conversations
Create a Metric off Transaction Table to sum by period but filter by load date

Hello. I have imported a transaction list into Pigment that has two date dimensions. One is “Month” and is = to an accounting period. The second date dimension is the “Load Date” added automatically during the import. Each month, I will append to the data list so at the end, this list could have GL data by period for each load month. I am able to sum the data by period OR by load month but I’m not able to say which load I want to use. My current formula is like this:'02. Test Pivoted Push'.Amount[BY SUM: '02. Test Pivoted Push'.Department, '02. Test Pivoted Push'.'GL Account', '02. Test Pivoted Push'.Month, '02. Test Pivoted Push'.Subsidiary, '02. Test Pivoted Push'.Vendor]I want to add in a condition where the results will be based on which load month is selected (something like  ]'02. Test Pivoted Push'.'Load Date'= Set_Last_Opex_Push_Month] where ‘Set_Last_Opex_Push_Month’ will be determined by me. 

1
Lehtohen
Budding Pigmenteer
17 hours ago
D
Newly Minted Pigmenteer
Dastan S.Newly Minted Pigmenteer
asked in Questions & Conversations
Calculation with Aggregations

Hi Pigment community,I am trying to calculate our revenue per employee metric. The metric should show Rev/FTE by month, and then, when aggregated by quarter, it should take the total revenue for the quarter and divide it by average monthly employees. The issue is that, for quarter it is summing up the total employees instead of taking average. This is what I am getting in Pigment, for instance: But what I want is the following: I tried showing “Total Revenue” as % of metric “Employees”, but even there it is still taking the sum of 3 months employees. I created table with Quarter dimension only, which fixes this, but I wanted to have months there too.

2
D
Newly Minted Pigmenteer
2 days ago
K
Just Arrived
Krete J.Just Arrived
asked in Questions & Conversations
Automatic - scheduled Excel data import to Pigment - some easiest workaround?

I have read through most of the articles and questions. As I understand currently there is no simple way to upload data from Excel to Pigment transaction list by automatic schedule. But what would be easiest workaround? Is it even in the roadmap?We have Excel that business owners keep up to date and would like to load it daily to Pigment without anybody 

0
K
Just Arrived
2 days ago
V
Newly Minted Pigmenteer
VA NguyenNewly Minted Pigmenteer
asked in Questions & Conversations
Formula Playground needs to be more intuitive

We had to create a workforce roster of all employees with on target cash (or OTE) in local and USD and it was very challenging to do this. Internally, on our team, we tried to troubleshoot it ourselves and spent a few hours with no success. Then we engaged our solutions consultant to do this and it took him 25 minutes to go through the spiderweb of how to take local currency multiplied by current fx rates to get USD. In my past experience using other systems like Adaptive, this would take 3 minutes. I’m bumping this up because I think there is an opportunity to create an easier/more intuitive formula playground or builder for some calculations.

2
V
Newly Minted Pigmenteer
3 days ago
P
Seasoned Pigmenteer
pigment_enthusiastSeasoned Pigmenteer
asked in Questions & Conversations
Aggregate the metric's data based on a dimension

I am trying to consolidate the no. of projects assigned to an employee by below formula:('Assigned Project'[BY COUNTUNIQUE: 'd. Integer','d. Employee'])The metric is of dimensions: Project, Integer, employee .In rows, i have all the dimensions of the metric, but am getting the “1” as the result, and the result is correct if I keep only employees in the rows.I need to fetch the total no. of projects assigned to an employee. (Metric is having 3 dimensions: Project, Integer, employee ). If an employee has total 4 projects assigned, it should show: “4” as the output for that employee instead of “1” value in each of 4 rows.

1
P
Seasoned Pigmenteer
3 days ago
nancy
Seasoned Pigmenteer
nancySeasoned Pigmenteer
asked in Questions & Conversations
Maximum cardinality exceeded error

Hi Team, Could you please explain me about the error “Maximum Cardinality Exceed” and how can we resolve it? thank you all in advance

4
P
Seasoned Pigmenteer
3 days ago
V
Newly Minted Pigmenteer
VicoNewly Minted Pigmenteer
asked in Questions & Conversations
Error: Cannot access modalities of List 'Budget 2024' through Property 'Load Type' because it is not unique

Hi people, I’m trying to create this metric when I receive the error:   'Budget 2024'.Amount['Budget 2024'.'Load Type'IN ('Budget 2024'.'Load Type'."Budget TopLine",'Budget 2024'.'Load Type'."Budget Other Costs")][BY SUM: 'Budget 2024'.'Country Input'.'Country Pigment', 'Budget 2024'.Month, 'Budget 2024'.Product, 'Budget 2024'.Service, 'Budget 2024'.'Budget Line']+'Budget 2024'.Amount['Budget 2024'.'Load Type'= 'Load Type'."Budget OpEx"][BY SUM: 'Budget 2024'.Month,'Budget 2024'.'Budget Line'][BY: 'Product Input'."TBD", Service.Name."TBD", 'Country Pigment'."Unallocated Country"]  I highlighted in red what Pigment is displaying, but I have no idea what’s going on.  I wil really appreciate any help. 

6
V
Newly Minted Pigmenteer
3 days ago
P
Seasoned Pigmenteer
pigment_enthusiastSeasoned Pigmenteer
asked in Questions & Conversations
Get the dimension list value based on a metric

Hi,I have a metric Project Allotted of data type: Project dimension;metric having dimensions: Employee, Project Role and Integers.how can I fetch the employee name in a metric “Emp” of data type: Employee and with dimensions: Project Role and Project from the above ABC metric and with Lastnonblank integer? 

6
oliverlee
Budding Pigmenteer
4 days ago
V
VianneyOccasional Observer
asked in Questions & Conversations
Can Pigment recognise a date dimension list if not linked with the native "Day" Dimension in Pigment ?

Hello hello Pigment communityI am struggling over an issue and maybe someone can help me. My goal is here to setup a dashboard to analyse our commercial pipeline. So all the client listed in our pipeline are potentiel leads. We have in this data several columns of date : last interaction date, last modified date, expected close date, etc… with also another column : “extract date” indicating the date of the extract of the whole data. The difficulty is that i cannot link all of theses column with the “Day” dimension in Pigment because i can’t have in the same metric several dimension linked with the “Day Dimension”. Hence i will have to create dimension for all of theses columns, not linked to the “Day dimension”, in order to be able to use them in my metrics. But my question is : if i create a dimension in which my data is several “date”, will pigment be able to recognize and organize it (in a graph for example) if the data is not linked with a native Pigment dimension ? For example, if

6
V
4 days ago
C
Newly Minted Pigmenteer
CindyNewly Minted Pigmenteer
asked in Questions & Conversations
Push data from Google Sheet with pivot option?

To push data from Google Sheets, must the data sheet be in tabular form? When we perform a manual csv file import directly to a transaction list, one of the options under “data layout” is to select “flat” or “pivot”. However, with the push feature, are we limited to just a flat file layout?

2
C
Newly Minted Pigmenteer
5 days ago
Ola
OlaFrequent Reader
asked in Questions & Conversations
Calculating Variance percentage for YTD values

We are trying to create a Income Statement for actuals vs Budget and previous years (PY in the table below) value. Showing both monthly value and YTD. We have our metrics on the rows in the table below. YTD is created as a calculated item using Cumulate and all PY columns are also created as calculated items, using an Offset on version Actuals. The problem is that I can’t get the column to the very right “Var PY %” to show the right value, no matter how I define the calculation priority. If I put it before YTD it is blank and if I put it after YTD it calculates the variance as a percentage for each month and then sums all these percentages together which results in a totally incorrect value. Any ide how to set up the calculations to make this work?  

3
Gabriel Amaro da Luz
Employee
5 days ago
P
Seasoned Pigmenteer
pigment_enthusiastSeasoned Pigmenteer
asked in Questions & Conversations
ANYOF function

I have a XYZ input metric of data type: Boolean with dimension: Teams andanother metric ABC of data type: number with dimension Manager.I need to create a metric at Manager level with dimension : Manager and data type: number , to fetch the amount from ABC if any of the teams of a specific manager is True.There is already a mapping between manager and teams in the Teams dim list. I tried this formula, but it gives the value even all the teams of a manager in XYZ metric is false. IF(ANYOF('t. XYZ [Input]'),'m. Amount')

5
RoryC
Community Manager
6 days ago
T
Newly Minted Pigmenteer
Tom S.Newly Minted Pigmenteer
asked in Questions & Conversations
Conditional Formatting applying to totals, how to not include?

Hi Pigment Community,I’m trying to apply conditional formatting to an entire column in a table but not include the Total line, which already has formatting of its own.  Is this possible?Thanks for your help,Tom S. 

3
sethbradley
Seasoned Pigmenteer
9 days ago
R
Newly Minted Pigmenteer
rykasaNewly Minted Pigmenteer
asked in Questions & Conversations
Referencing the same number in a month over time

I have Services Bookings that I need to turn into Services Revenue over a set of months based on % of completion. Each Services Bookings (ex: $100K) relates to a product (Product A) that has a set number of services hours (ex: 300 hours) that amortize over a set of months (ex: month 1=50, month 2=150, month 3=100). To calculate revenue by month, I need to multiply Revenue $ per Hour ($100K/300 hours) by hours in each month. How do I reference the same Revenue $ per Hour in each of the 3 months? As I book deals over time, each month will have different Services Bookings that will impact the Revenue $ per Hour for that Services Booking Month. 

3
Nathan
Employee
9 days ago
P
Seasoned Pigmenteer
pigment_enthusiastSeasoned Pigmenteer
asked in Questions & Conversations
Boolean metric - True, False, Blank

I have a metric with so many IFs conditions,Out of those, there’s one condition which checks the other boolean metric is False Or Is Blank.ISBLANK(Is active?) : I am using this logic, its giving me the cardinality error.If I am replacing it with Is Active <> TRUE, it doesn’t give the error ; but it checks only the values which aren’t true, i.e. False, and doesn’t checks the Blank values.Any suggestions? to resolve this as I need to check both “((Active? = FALSE) OR ISBLANK(Active?))”

3
CDALMAY
10 days ago
P
Seasoned Pigmenteer
pigment_enthusiastSeasoned Pigmenteer
asked in Questions & Conversations
Formula not working as expected - TEXTLIST

In Pigment, I have tried the below formula for City metric: The source metrics are with dimension: Employees and RoleThe target metric is having dimension: Employees and Month.I’ve tried the above formula, but it’s giving me the same abc value across for employees and for all months.Can anyone assist me with this formula in Pigment IF(ISDEFINED('erCity'.Name[BY TEXTLIST: TIMEDIM('erStart Date',Month)]),'erCity'.Name[BY TEXTLIST: TIMEDIM('erStart Date',Month)],IF(ISDEFINED(PREVIOUS(Month)),PREVIOUS(Month),'erCity'.Name[BY TEXTLIST: TIMEDIM('erPlan Date',Month)])) IF(ISNOTBLANK('Employees by Role'.City[TEXTLIST: 'Employees by Role'.Start Date])THEN 'Employees by Role'.City[TEXTLIST: 'Employees by Role'.Start Date]ELSE IF ISNOTBLANK(PREVIOUS(City)) THEN PREVIOUS(City) ELSE 'Employees by Role'.City[TEXTLIST: 'Employees by Role'.Plan Date]City metric in Anaplan with above formula. 

5
P
Seasoned Pigmenteer
10 days ago
MagnusKnapp
Just Arrived
MagnusKnappJust Arrived
asked in Questions & Conversations
Update a dimension list text property from a transaction list

When we are importing data into a transaction list, and the data contains new dimension list items those are automatically added to the corresponding dimension list. However the new item is only added with the unique ID, and all properties (Name) are blank. Is there a way to get the dimension list name property (text) updated (even for already existing items), similar to when a csv-file is manually imported to a dimension list? Would be really nice to get an answer on this. 

1
Stef
Employee
12 days ago
Ola
OlaFrequent Reader
asked in Questions & Conversations
Combine "Show value as" with aggregations

We have created an income statement where we have one metric for Income and another metric for COGS. Both these metrics is by the dimension Account and in our table we use Account Group and Account on the Rows which gives us a  dynamic income statement where it is possible to expand and collapse rows to see the hierarchy:- Income      - Account Group              -AccountOur challenge is that we like to have the exact same view that shows us Income and COGS per Order. We can achieve this by using “Show value as” to show our metrics in relation to total number of orders and it also works fine when filtering the table by Location or Subsidiary. However, show value as does not work in combination with grouping by Account Group and Account, since number of  orders is not by account. If we add the dimension Account to our metric for Number of  orders we can get it right on the lowest level, but in this case the aggregations by account and account group becomes wrong. Is there any workaround

3
CDALMAY
13 days ago
P
Seasoned Pigmenteer
pigment_enthusiastSeasoned Pigmenteer
asked in Questions & Conversations
TEXTLIST with UNIQUE

I have a target metric wherein I need to fetch all the project names assigned to a person within 12 months.E.g. If the same project exists for multiple months in the source metric, in the target metric - it should return only the unique ones.I tried this formula, this is working fine and returning all the projects assigned to each person in all the months.But I want to fetch the unique projects.Source Metric has dimensions: project, role, monthTarget metric has dimensions: Project, role.'Project Assigned'.Name[BY TEXTLIST: 'd. Month'][REMOVE TEXTLIST: 'd. Month'] 

1
G
16 days ago
P
Seasoned Pigmenteer
pigment_enthusiastSeasoned Pigmenteer
asked in Questions & Conversations
Metric from another application within same workspace cant be published on the board.

I’m trying to publish a metric xyz which is coming from/created in ABC Application (Library → Sharing (ON) for this metric) in my another application PQR, but this isn’t allowing me to do so.Also, I have used this metric in PQR application in various fornulas and it’s taking up the source metric correctly.But when I’m trying to publish this metric on a board in other app- PQR, then in the dropdown, this metric isn’t showing up.

2
P
Seasoned Pigmenteer
17 days ago
C
Newly Minted Pigmenteer
CalvinNewly Minted Pigmenteer
asked in Questions & Conversations
Reverse Column Order in Table View

Is it possible to reverse the column order when displaying data in a table for a dimension such as month?For example, I have 12 columns of data by month and would like to display the table from left to right showing Dec-Jan instead of Jan-Dec.

2
C
Newly Minted Pigmenteer
19 days ago
K
Budding Pigmenteer
keerthanarameshBudding Pigmenteer
asked in Questions & Conversations
IFBLANK function for metrics

Hello,I want a metric to have another dimension which can be mapped based on 2 separate criteria.So I created 2 metrics separately with their respective criteria by using BY modifier to map the dimensions.And finally I wanted to combine them together so I used IFBLANK to use either one way to arrive at the final mapping.But the final metric seems to be duplicating some of the mappings and arrives at incorrect results. My goal is to have the metric values to be mapped to the second criteria only if it was not mapped through the first one.How can I implement this in a metric formula?IFBLANK has been working great for transaction list blank items, what would be the similar alternative to be used for metrics?

4
Lehtohen
Budding Pigmenteer
20 days ago
K
Budding Pigmenteer
keerthanarameshBudding Pigmenteer
asked in Questions & Conversations
Use BY modifier in a metric and retain both the dimensions

I have a metric with “Vendor”, “Sub Department”, “Entity”, “Expense Type” and “Month” as the dimensions.I want to add another dimension “KPI Group” to this metric based on “Vendor” and if its blank then based on “Expense Type”Vendor and Expense Type dimension has the link to KPI Group in their respective dimension list. So when I use [BY: ‘Vendor’.’KPI Group’] or [BY:’Expense Type’.’KPI Group’] this results in the the Vendor and Expense Type dimension getting converted to KPI Group. Instead I want to populate the respective KPI Group based on Vendor or Expense Type and arrive at a metric that contains all these 3 dimensions (Vendor, Expense Type and KPI Group) along with the remaining Sub Department, Entity and Month.How can I arrive at the above mentioned dimension structure for the metric?

2
K
Budding Pigmenteer
22 days ago
O
Newly Minted Pigmenteer
Oscar SalazarNewly Minted Pigmenteer
asked in Questions & Conversations
Trying to figure out how to use Exclude and Previous functions

Hi,In the picture below I´m currently using the formula: 'First Time Borrower'[by constant:Month."Jan 24"]*'Retention Curve'My First issue is that the multiplication of “first time borrowers” by “retention curve” in Jan 24 works perfectly, but for Feb 24 I need that the retention curve percentage used for the multiplication, starts with Jan 24 and not Feb 24, I mean the percentage for the formula in Feb 24 should de 100% that belong to Jan 24 and not the  82.76% that belongs to Feb 24. I tried using the “previous” function but it keeps telling me there are errors. My second issue is that I´ve been trying to use the exclude function in orden to have the final view as the picture below:I´m trying tu use Exclude to recreate the blank cells. For intersections Feb 24 and Feb 24 I use the formula:  'First Time Borrower'[by constant:Month."Feb 24"]*'Retention Curve'[EXCLUDE:Month."Jan 24"]But when I try the same formula in the next line (Mar 24) but try to include Feb 24 into the exclude func

2
O
Newly Minted Pigmenteer
23 days ago

Badge winners

  • Read and Interpret Formulas Certified
    Ekin Yelkenhas earned the badge Read and Interpret Formulas Certified
  • Emerging Author
    Cindyhas earned the badge Emerging Author
  • Emerging Author
    RoryChas earned the badge Emerging Author
  • Read and Interpret Formulas Certified
    cLoiseauhas earned the badge Read and Interpret Formulas Certified
  • Pigment Modeling Fundamentals Certified
    Sylvainhas earned the badge Pigment Modeling Fundamentals Certified
Show all badges
Terms & ConditionsCookie settings