Skip to main content

Questions & Conversations

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

  • 913 Topics
  • 2,419 Replies
913 Topics
Cat M
Apprentice Author
Cat MApprentice Author
asked in Questions & Conversations
Using IF and AND function together in a formula?

Hi, I want to use the AND function together with the IF function in one formula in a property of a transaction list. Is this possible in Pigment? I can’t seem to get the AND part to be recognised when using it as part of the logical test of an IF statement, the way you can in Excel. In Excel for example I can write an IF statement using AND:=IF(X2>7,"check",IF(AND(E2="Direct",M2="Retail O&O",X2>0),"check","OK")) But in Pigment when I type AND as part of my IF statement, the function doesn’t seem to be recognised and my formula doesn’t work. The text for ‘AND’ doesn’t show up in green the way it does for the IF function (or other functions.) I don’t have any issues using the IF function otherwise, or nesting multiple IFs in a formula, it’s just that I can't seem to use IF with AND. Please let me know if it’s possible, or if perhaps I need to write it in a different way? If not possible I suppose I can create a separate property to do the AND check. Thanks,Cat

3772
Cat M
Apprentice Author
M
Wallflower
mmusabWallflower
asked in Questions & Conversations
Calculating forward sum of values

HiI want to sum the values from my current starting point to the last value available in a metric. As you can see first value is 0.22 and last is -30,993,214 and sum of all those in middle will be 30,669,213 something.that metric called inp_when to sell asset differs for each sage entity asset. How can i go about doing this calc in simple way? I tried moving sum etc but doesnt work. 

5164
M
Wallflower
E
Master Author
ElodieMaster Author
asked in Questions & Conversations
Modeling lock budget

Hello,I have created the budget based on historical data (budget version based on actuals version up to a switchover date), but at each closing period and when we import the new data, the historicals can vary so it makes my whole budget move… The uploaded data for the actuals is imported into several transaction and dimension lists.It is not optimal as the budget should be closed as of now and not moving. Has anyone encountered the same issue? The idea would be to be able to use the budget version in comparison tables so it needs to be fixed in the app.Hope this makes sense!Thanks,Elodie

64711
E
Master Author
M
Trendsetter
MKSMPBTrendsetter
asked in Questions & Conversations
Applying a specific months value to all months in a year

Hey,I am trying to reference the headcount for December and apply it to all months in the relevant financial year as part of a calculation. i.e if head count in Dec 24 in 100 and Dec 25 is 150, the formula would show 100 for all months in 2024 and 150 in all months of 2025.Has anyone done anything like this before and found a solution? Thanks! 

2991
francois
Employee
Y
Master Author
YaejisMaster Author
asked in Questions & Conversations
Saving calculation

Hi! Can someone please help with setting up the formula for below? Thanks in advance!----Dates:Given Data (yellow highlighted) Question: I am seeing for formula to get the result below yellow highlighted row, “Result”.  The calculation starts from 3rd months from the start date, 10/31/2022. So it needs to calculate “January value 18,792 minus start date value 18,792. The result for 2023 Jan is zero. Then, calculation is same concept but moves forward. It’s always deducting the start date value, 18,792.  For July 2023, calculation is July 2023 19,355 minus the start date value 18,792, resulting 564.The calculation should stop in Bridge end date, 12/31/2023.   Thank you!

3903
Y
Master Author
P
Trendsetter
PaylwardTrendsetter
asked in Questions & Conversations
Demo Ideas

Hello, I have gone through all of the available training and videos for Pigment modules and setting up Demos. I would like to know if there are any additional Demo resources and data I can use to test my skills.

2652
KarlW
Community Manager
C
Wallflower
Carolina LeeWallflower
asked in Questions & Conversations
Balance Sheet: Net Income

Which formula can I use for Net Income at my balance sheet in the begging of the year where I need to transfer all Net income to Retained earnings / losses and start accumulate again? See below an example. I read this article below but I couldn’t understand: 

4534
P
Trendsetter
M
Wallflower
mmusabWallflower
asked in Questions & Conversations
Repeating set of values until the a certain condition

Hi TeamI have a calc which uses Year dimension and the year starts from 1 to 20. There is an assumption metric which defines what year the crops will be harvested. So lets say currently the assumption is year 6 in which the crops will be harvested, then from that year onward second rotation will start and then follow similar process until Year 6 where it again will be harvested and planted again.So as an example in excel, you can see from FY19 the year starts as 0 as that is where initial plantation happen. Then it counts from Year 1 to 6 and then at Year 6 which is equal to our assumption metric (Year 6) as well, it stops and then a new cycle starts from 0 to 6 all over again. How can we built something like this easy way? I have tried thinking of using MOD in which lets say in our Year dimension, we have count column as well that goes from 1 to 20 . So what i am doing is saying if Year.Count/Assumption metric =0, then mark it as harvest year.  Now how can i get it to count and reset

1861
Issam Moalla
Employee
P
Wallflower
ParapebuneWallflower
asked in Questions & Conversations
How to create a weighted distribution

Hi!I have a sales volumes metric with dimensions Category, Channel, Product and Month.Data was imported from a transaction list.I want to adjust this with some volumes that are manually input by user per channel, but I want to spread this values on every product using the same weighted distribution per category and month as the initial data. For example, if on month Jun, on a product, the volume was 15% of the total, I want to add on that product for Jun 15% of the manually added volume.How do I achieve that?Thanks in advance!

1961
S
Trendsetter
M
Wallflower
Moosa MalickWallflower
asked in Questions & Conversations
Removing Blanks in Transactions List

Hi,Is there a way to remove blank cells in the grid of a transactions list. I am applying a filter to the transactions list which gives me blank rows which I want to get rid of. Thanks

5044
M
Wallflower
M
Wallflower
Moosa MalickWallflower
asked in Questions & Conversations
Filtering a dimensions list based on another dimensions list.

Hi,Is it possible to filter a dimensions list based of another dimensions list e.g. if I want to just filter for Revenue, can I filter based on the classification field which comes from another dimensions list.

1261
Nathan
Employee
E
Author
ELeff_AccAuthor
asked in Questions & Conversations
Compile Commentary from different tables

Does there exist a way to take the commentary (a metric of type text) from across two different tables? For instance, commentary about an Opex Expense related expense, for example Travel Expense (table 1) and Employee Expense (table 2).  Each table has a different set of metrics and each allows for text entry to supplement the expense to provide additional detail.  Downstream the request is to compile all expenses in a FPA view of all expenses entered across the company and then additionally to include the expense detail (text) with the relevant expense lines.  Bringing the data from the two different tables is no issue as we can use the “+” operand to include data from both tables.  This presents an issue when attempting the same with Text as the metric type of text does not accept Text as an arithmetic operand.  I attempted to utilize & but that did not align properly to the relevant line items being planned.  Curious if anyone else has come across this...

2163
E
Author
D
Master Author
DastanMaster Author
asked in Questions & Conversations
Dynamically Aggregate items in Chart views

 Hi Pigment friends, I wonder if there is a way to dynamically aggregate calculated values when using multiple items in a dimension? I am sure my question is confusing, so hence, I will give an example.We are presenting Average Contract Value (ACV) in each segment throughout the year on a monthly basis (we have 3 segments):Each node in the chart is the ACV and all segments are currently selected. The thing is, when we want to represent aggregated ACV with all segments, we need to create a separate chart: The downside of this chart is that we cannot dynamically add/combine/remove segments, because it will start summing them up, instead of showing ACV of combined segments. For instance, if I select Small customers ACV ($500) and Medium customers ACV ($1000), and we have 10 small customers and 5 medium customers, the average aggregated ACV should be: (10 * $500 + 5 * $1000) / (10 + 5) = $667. With aggregated chart, it will sum $500 and $1000 and show $1500 as total ACV, if Small and Mediu

3642
D
Master Author
P
Wallflower
ParapebuneWallflower
asked in Questions & Conversations
Manual Input Board

Hi! I have a numeric metric of volumes of selling items with dimensions: item, channel, category, monthFor this metric I have some volumes imported from a transaction list. Apart of these volumes I want to use an input board which allows me to manually input extra volumes per item/channel/category/month which will finally add to the imported volumes.In the board, the input widget should show all the items that were manually input and should have the option to input a new volume based on the dimension selection.How do I  do that?Thanks in advance!

4045
Issam Moalla
Employee
D
Master Trendsetter
dariousMaster Trendsetter
asked in Questions & Conversations
Calculating Adjusted Average by Month on various Dimensions

Hello Pigment Community!I hope this post finds you well. I'm currently working on enhancing my model's performance by calculating an adjusted average by month for deal completion times. While I've successfully calculated the average number of days for deal completion in a straightforward manner, I'm now facing challenges taking it to the next level. I'm reaching out to this community for assistance, and I would greatly appreciate your input and expertise.Current Solution:In my current approach, I utilize a transaction block named "All Deals," containing two columns - 'create date' and 'close date.' Using the 'DAYS' pigment function, I calculate the difference between these two dates. Then, using Excel I compute the 1st and 3rd quartiles using the excel function “QUARTILE.INC”. I then calculate the the Inter-quartile range (IQR), which I use to calculate the upper and lower bounds for my data. I then manually input the upper and lower bounds in my pigment solution to exclude outliers an

4571
Issam Moalla
Employee
J
Master Trendsetter
JoseMaster Trendsetter
asked in Questions & Conversations
Getting the seasonality for FORECAST_ETS function

Dears,I have the historic sales per month, and we have strong seasonality, but also and upward trend. I would like to use the FORECAST_ETS function, however, I don’t understand how to get the value of seasonality. For example I know every July and August we have a drop and then it gets higher on September. In this case the seasonality would be 12? If I want something more exact can I use Python and then get the equation and plug in pigment?  For calculating a better seasonality number, alpha, beta and gamma, is there a way to get those numbers in Pigment, or I need to have another application, such as Python or R? Thanks in advance! Jose

5472
Jurgen Maas
Master Trendsetter
L
Author
Lester ChowAuthor
posted in Questions & Conversations
How are you all doing budget transfers it doesn't seem to exist as a function

We can’t edit snapshots and scenarios always have a risk of changing if underlying metrics change. How do you all do budget transfers? ie. I need to move a headcount from marketing to finance, how would I do this? And I would want all the related headcount costs to move too in the snapshot.

1511
Jurgen Maas
Master Trendsetter
K
Apprentice Author
kristian.bartelApprentice Author
asked in Questions & Conversations
Budget Scenario or Version?

Not sure if this exact topic shows up elsewhere, but after some digging, I was still interested in clarifying best practice for budgeting in Pigment (scenario vs. version). I found this in the responses of a similar post:  For versions vs scenarios, this is a hot topic. We are right at the moment where I would suggest to use scenarios. Knowing there is currently a constraint: one scenario cannot reference another via a formula (we are working on it though). besides that, it behaves mostly the same as using a standard dimension for versions, except that you can easily compare to snapshots (thus fixing your numbers). With my limited understanding it seems like scenarios are preferable in that they respond to changes in the model over time (i.e. if there’s a change in dimensionality in a metric, the new dimensionality is accessible through your budget scenario), and they are very visible when selecting “scenario” in pages or elsewhere. However, given that the nature of a budget is static,

5682
Jurgen Maas
Master Trendsetter
D
Master Trendsetter
dariousMaster Trendsetter
asked in Questions & Conversations
Seeking Help for Calculating YoY Moving Average and Building Growth Hypothesis

Hi Pigment Community! I hope this message finds you well. I am currently working on building a table that will display my Year-over-Year(YoY) Moving Average(MA) of opportunities to customers. The ultimate goal is to use these calculations to create a growth hypothesis, which will then be employed for forecasting future opportunities and customers. I want to ensure that my YoY MA calculations are not only viewable at all levels of granularity but also correctly calculated for both granular and total values. I'm facing two main challenges that I'm seeking assistance with.Problem 1: Granular and Total Value Calculation I want to create a calculation that accurately computes both granular and total values without the aggregation issues I'm currently experiencing.Problem 2: Referencing YoY MA Values for Future Growth Hypothesis I aim to reference YoY MA values in future metrics for the growth hypothesis without being hindered by the limitations of calculated items and 'show value as' functi

7332
D
Master Trendsetter
P
Author
PatMacAuthor
asked in Questions & Conversations
Value formatting in Charts

Is there a way to get rid of the decimals in the below chart without reverting to grid form first?  

2355
Benoit
Community Manager
A
Master Author
AlexandreMaster Author
asked in Questions & Conversations
How to compute a median ?

Hi,How can I do a median in Pigment ?Thanks

6003
J
Master Trendsetter
A
Master Author
Antoine-2Master Author
asked in Questions & Conversations
Implementing a Default Filter on board based on last month imported

Hello,I would like to create a default filter for a board that takes the last month into account based on data imported.Is it possible ? And if so, through what functionality?Thank you for your help.Have a nice day,Antoine

1871
Issam Moalla
Employee
M
Wallflower
Mart.Wallflower
asked in Questions & Conversations
Creating a dimension list and transaction list

How do I create a dimension list (step by step process), How do we know what fields would be a dimension, for example, would a product type classified as text or dimension? or customer classification (New/Existing) be a text or dimension.Also, how does this feed to the transaction list, when importing a csv file.When the transaction list is then created, how do I create metrics, chart and tables from this transaction list.

2581
Nathan
Employee
C
Author
connorsmithAuthor
asked in Questions & Conversations
Hide the Secondary Axis

Hello, is there a way to hide the secondary axis? I see that there is optionality to hide the left Y axis (screenshot 1), but I don’t see anything to hide the right Y axis. On my reporting boards, I notice that the right Y axis takes up a lot of space and forces the remainder of the valuable content to be squished (screenshot 2). I’d imagine that others would have this same problem?. 

1241
Andrea
Employee
P
Trendsetter
PaylwardTrendsetter
asked in Questions & Conversations
Build from Scratch Demo

Hello,  I am a new user with Pigment and am following the instructions in the Build from Scratch Demo lesson.  I have followed along up until we upload the transaction list.  When it is complete and I click on the transaction list title “MYERP” from the upload, there is no dimension data for Country, Region, Sub Region, Products and Date.  I see the units sold but that is about it.  I followed along with the video but am now getting blanks.  Can someone help with this error.

4882
P
Trendsetter

Badge winners

  • Pigment Like a Pro
    ThierryGThas earned the badge Pigment Like a Pro
  • Pigment Modeling Fundamentals Certified
    ThierryGThas earned the badge Pigment Modeling Fundamentals Certified
  • Partner Solution Consultant Certified
    liuhas earned the badge Partner Solution Consultant Certified
  • Pigment Modeling Fundamentals Certified
    ccifuenteshas earned the badge Pigment Modeling Fundamentals Certified
  • Read and Interpret Formulas Certified
    Filippo Rossihas earned the badge Read and Interpret Formulas Certified
Show all badges
Terms & ConditionsCookie settings