Skip to main content

Explore the Community

Connect with other users, discuss how you're using Pigment, and get inspired

1058 Topics
N
Wallflower
Noella ThierryWallflower
asked in Questions & Conversations
Transfer value from one quarter to another without values moving when changing switchover dates

Hello!I want to transfer values from one quarter to another, based on certain conditions written by IF[...] and I am using filter and select as shown below:[filter: 'Actuals & Dimensions (Datahub)'::Quarter.'Quarter -1'][Select: 'Actuals & Dimensions (Datahub)'::Quarter-1])))However, when switching the switchover date, the values that I moved from Q to Q+1 will not be in Q+1 anymore. do you have an idea of a better clean way to code this?Thank you very much :))

625
Nathan
Employee
A
Author
A_GarneriAuthor
asked in Questions & Conversations
How "RANK" (Sequential), ranks?

 Hello Pigment,I would like to ask for your help regarding the RANK function, as I don’t fully understand the logic behind the ordering of its rank.Initially, I thought it considered the order of the list used as a dimension; that is, if my first 5 values to rank are equal, my rank 1 would be the first item in the list. However, this doesn’t always work that way. Here’s an example: Ideally, I want to create something like "ISFIRSTOCCURRENCE" that allows me to identify the first value created (like in Anaplan). My initial approach was essentially that: if my rank value is 1, then that is my first occurrence. However, I can’t achieve this correctly.What is the logic behind using "Sequential" as argument then? How does it behave? Or how does it find the values to rank? Do they rely on the ID behind the list items when the ranking attributes are the same?I found a post where they created a list of values to rank and used BY FIRSTOCCURRENCE, but that would require creating the list every ti

2123
Gabriel Amaro da Luz
Employee
Louie
Author
LouieAuthor
asked in Questions & Conversations
Previous Function with Multiplication

Hi Pigmenters,Any suggestions on how to write a more elegant formula to achieve the results shown in the Excel please.Here s the current solution: ifblank(ifblank(Metric, 1)*previous(Month), Metric) Thanks, Louie 

963
Louie
Author
A
Wallflower
Antonin GRWallflower
asked in Questions & Conversations
Create a block through an API

Hello,  I am exploring API’s capabilities in Pigment and by now I found that API can be used to : Import / Export data  Manage users in the case we use SCIM  Monitor application Audit logsI did not find any documentation about managing (create, modify, delete) blocks. For example, I would like to create a metric. Is there a way to do it through an API ?  Thanks a lot for your answer, Antonin

562
A
Wallflower
W
Master Trendsetter
Weining BenMaster Trendsetter
asked in Questions & Conversations
Count number of occurrences and mark the last occurrence

Hello,I currently have the following table, in the first column I fill in tickets manually.And now I have two needs:I hope to count the number of corresponding Tickets through the second column. I hope to mark the last ticket in sequence.I tried using the Rank function, but couldn't achieve these two needs accurately. Can you give me some suggestions?Thank you in advance!!

3385
A
Author
R
Apprentice Author
RohitDivate_GustoApprentice Author
posted in Questions & Conversations
Looking to make a SWITCH?? Hiring a Full Time Pigment Solution Architect in NYC for Gusto

https://boards.greenhouse.io/gusto/jobs/6286911We have been customers for 1+ year and have already done the implementation. We love the tool a lot! We have a lot of net new builds and enhancements we want to do, so plenty of interesting problems to tackle. Gusto is $10b private SaaS company on the path to IPO, so a very exciting place to be and this role will directly report to me in Finance.Additional details:NYC based → we have most of the team there, so would like someone 3 days in office. We have relocation benefits. Level → flexible based on experience Visa Sponsorships → TBD, if we find a great candidate, we might sponsorApply on the link above AND directly email me at rohit.divate@gusto.com Thanks!

670
R
Apprentice Author
C
Author
CalvinAuthor
asked in Questions & Conversations
Successful Data Import Notification for Multiple Selected Users

I’m wondering if it’s possible to send out a notification to multiple users when a successful data import has been completed? Ideally by e-mail or through Slack. In my experience so far only the user that initiated the data import receives the notification.

502
C
Author
nancy
Wallflower
nancyWallflower
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

10705
I
Apprentice Author
D
Apprentice Author
danishnoorani1Apprentice Author
asked in Questions & Conversations
Pro Training: Workforce Planning EE_Load_HRIS Date configuration

The hire date for EE00001 is 10/01/2022, but when I apply the formula, it shows as January 2023. Am I missing something?EE_Load_HRIS:   

2004
sethbradley
Master Trendsetter
C
Author
CindyAuthor
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. 

1882
C
Author
D
Apprentice Helper
Dastan S.Apprentice Helper
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.

2122
D
Apprentice Helper
V
Apprentice Author
VA NguyenApprentice Author
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.

1602
V
Apprentice Author
Chahat
Master Author
ChahatMaster Author
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.

461
Chahat
Master Author
V
Apprentice Author
VicoApprentice Author
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. 

1606
V
Apprentice Author
Chahat
Master Author
ChahatMaster Author
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? 

1406
oliverlee
Master Helper
V
Trendsetter
VianneyTrendsetter
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

1436
V
Trendsetter
C
Author
CindyAuthor
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?

642
C
Author
Tatiana
Wallflower
TatianaWallflower
published in Company News
🎊 Congratulations to our September 2024 Pigment Academy Champions! 🎊

Dear Pigment Community,We continue to publish updates on our Pigment Academy champions to recognize their outstanding achievements and award the Pigment Academy Badge 🏆We’re excited to announce our top performers for the month of September: 🏆 Top 5 Active Users by Course Completions:@yzwong  : 54 completions @Emeline : 39 completionsPaulina Bochenek : 31 completionsCharles Blandino : 29 completions Carlos Hernanz Garcia: 26 completions Congratulations! Your hard work and dedication is truly inspiring. We're looking forward to celebrating more achievements in the coming months!

540
Tatiana
Wallflower
Ola
Master Author
OlaMaster Author
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?  

2113
Gabriel Amaro da Luz
Employee
Chahat
Master Author
ChahatMaster Author
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')

1855
RoryC
Community Manager
T
Apprentice Author
Tom S.Apprentice Author
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. 

723
sethbradley
Master Trendsetter
R
Wallflower
rykasaWallflower
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. 

733
Nathan
Employee
Chahat
Master Author
ChahatMaster Author
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?))”

2533
CDALMAY
Master Helper
Isabella Leslie-Miller
Community Manager
Isabella Leslie-MillerCommunity Manager
published in Company News
🎓 Pigment Academy: Elevate Your Expertise with Exclusive Modules!News

Dear Pigment Enthusiasts,We’re excited to announce a new lineup of educational modules tailored specifically for customers on our Pigment Success Plan. Whether you’re just getting started or looking to deepen your expertise, our academy offers the perfect opportunities to elevate your skills and make the most of Pigment’s powerful capabilities.1. Live Sessions: Learn Directly from the ExpertsJoin our expert-led live sessions and immerse yourself in the advanced capabilities of Pigment. These interactive sessions are designed to help you master key functionalities and apply them effectively within your organization. Here’s a sneak peek at some of the sessions available:Importing Data 101: Importing data in Pigment the right way is going to save you a ton of time. Understanding the ins and outs of this critical step will literally make your life better. Troubleshooting 101: How does it feel when you finally find the issue that was causing that pesky data discrepancy? Amazing, right? Join

930
Isabella Leslie-Miller
Community Manager
Chahat
Master Author
ChahatMaster Author
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. 

1245
Chahat
Master Author

Badge winners

  • Author
    Kelvin Chanhas earned the badge Author
  • Pigment Modeling Fundamentals Certified
    Alanhas earned the badge Pigment Modeling Fundamentals Certified
  • Pigment Like a Pro
    Alanhas earned the badge Pigment Like a Pro
  • Read and Interpret Formulas Certified
    Maureenhas earned the badge Read and Interpret Formulas Certified
  • Read and Interpret Formulas Certified
    Alderthas earned the badge Read and Interpret Formulas Certified
Show all badges
Terms & ConditionsCookie settings