Forum Stats

  • 3,679,580 Users
  • 57,523 Discussions
  • 190,817 Comments

Discussions

Check out Oracle NetSuite upcoming events and conferences here
NetSuite Maintenance - September 25
Please note that on Saturday, September 25, 2021, at 10:00 PM Pacific time, our Case Management System will undergo a scheduled maintenance for approximately 180 minutes. During this time, case creation via SuiteAnswers will be unavailable and inbound calls will be routed to Customer Service.
Cheers to 1️⃣0️⃣ years of #SuiteWorld! 🎉 We’re celebrating by extending the Super Saver Pass AND launching the digital On Air experience TODAY! Whether you join us in-person or digitally, we can’t wait to make even more memories with you. https://lnkd.in/dGpPpmUV
Webinar | COSTING 101: Learning the Basics of Inventory Costing webinar was a success! Please take a minute to fill out this short survey. For those who missed it - Don't worry! Here's the link to the recorded session!
NetSuite Support Assistant: A quicker way to get answers to your basic NetSuite questions
The NetSuite Support Assistant is a convenient chatbot SuiteApp that provides quick responses to your basic questions directly from your NetSuite Account.
Click NetSuite Support Assistant Now Available for more details.
Release Preview 2021.2

If you would like to receive a 2021.2 Release Preview account, please opt -in to take advantage of the Release Preview access period to prepare and become familiar with the new NetSuite capabilities. For details, see Requesting a Release Preview Account.

If you receive a Release Preview account in 2021.2, you can access it several weeks before your production account is scheduled for an upgrade to the new release of NetSuite. Notifications with details about Release Preview availability are sent to the account administrators as described in Release Preview Notifications. Release Preview availability information is also posted on the New Release portlet.

Gross margin % custom KPI meter

Daniel V
Daniel V Member, forum_userscommunity_users Posts: 23 Newbie

Hi all,

First time question. I want to create a custom visual KPI meter for gross margin. Would really appreciate assistance creating the saved search and how to add to the KPI meter portlet drop down selection.


Tagged:

Best Answers

  • Patrick Fresnosa-Oracle
    Patrick Fresnosa-Oracle Member, forum_netsuite_mod, ORACLE_EMPLOYEE Posts: 377 Bronze Trophy

    Thanks Emi!

    Hi @User_4KSCX,

    Thank you for the screenshots may I know what values or column in the custom Income Statement are you comparing?

    Here are the possible reasons as to why your Income Statement are not in line with the Save Search:

    1.) The custom Income Statement might not include all (Account Type) Cost of Goods Sold and all Income Account

    2.) In the criteria tab of your Saved Search, it seems that the Posting is not defined, kindly set it to true so that it can only capture the posting transactions

    3.) Since you have selected a date filter, it refers to date of the transaction and it is possible that your Income Statement is filtering out based on Accounting Period. To be able to have your reports based on the date of transaction you can do the following:

    A.) Customize the Income Statement Report and add a Date Filter (Customize Report > Go to Filters > Financial folder > Date) and select a date

    B.) You can also set the Report by period set to Never in order for your Financials to have a date filter.

    Note: When you select Financials Only or Never, you may encounter problems with data for KPI scorecards that use accounting periods. However, for the Custom KPIs the basis is based on the transaction date as this is still an enhancement under 382241 Home Dashboard > Key Performance Indicator > Custom KPI > Ability to select Period-Based Range and Compare Range (e.g. This Period, Last Period, etc.). Apologies for this system limitation

    4.) If you are using Oneworld, it is possible that some unwanted subsidiaries are included in either your Saved Search or your Custom Income Statement

    -------

    Here is how I was able to accomplish creating a custom KPI for the Gross Profit % in my test account:

    1.) I have created a Saved Search with the following criteria:

    (I only want in the KPI/Saved Search to show transactions for my Canada Subsidiary so I added a criteria)

    2.) Added the following in the Results tab:

    I added the Gross Profit % on the top of the results so that it will be looked up by the Custom KPI.

    The formula are as follows:

    Gross Profit%:

    SUM((CASE WHEN {accounttype}='Income' THEN {amount} ELSE 0 END)-(CASE WHEN {accounttype} = 'Cost of Goods Sold' THEN {amount} ELSE 0 END))/SUM(NULLIF((CASE WHEN {accounttype}='Income' THEN {amount} ELSE 0 END),0))

    Total Income Accounts (Optional)

    (CASE WHEN {accounttype}='Income' THEN {amount} ELSE 0 END)

    COGS (Optional)

    (CASE WHEN {accounttype} = 'Cost of Goods Sold' THEN {amount} ELSE 0 END)

    Gross Profit (Optional)

    3.) Also a date filter in the Available Filters (Optional for checking)

    (CASE WHEN {accounttype}='Income' THEN {amount} ELSE 0 END)-(CASE WHEN {accounttype} = 'Cost of Goods Sold' THEN {amount} ELSE 0 END)

    3.) I then added the Saved Search in the KPI Portlet

    a.) Navigated to Home

    b.) Set Up the KPI portlet

    c.) Add Custom KPI

    d.) Selected the Saved Search

    e.) Save

    Result:

    Hope this helps 😀

    ===========

    If you find this reply to your question as helpful, others with the same question might find it helpful as well. By marking “Yes” on the “Did this answer your question”, you’ll be able to aid the community to find the solution much easier and faster without the need to read through all the replies.

  • Ivy Lopez-Oracle
    Ivy Lopez-Oracle Member, forum_netsuite_mod, ORACLE_EMPLOYEE Posts: 780 Bronze Crown
    IPv6 Accepted Answer

    Hi @User_4KSCX,

    Kindly check your saved search if it has a Date filter in the Available Filters tab of the saved search.

    As mentioned in SuiteAnswers 8111, this is one of the requirements of the Saved Search in order to use it for comparison on a Custom KPI:

    • Not include any date fields defined as filters on the Criteria subtab
    • Have only one field with a summary type (such as group, sum, or count) defined on the Results subtab.
    • Have a date field defined as an available filter on the Available Filters subtab.

    Hope this helps! 😊

    -Ivy

    ===========

    If you find this reply to your question as helpful, others with the same question might find it helpful as well. By marking “Yes” on the “Did this answer your question”, you’ll be able to aid the community to find the solution much easier and faster without the need to read through all the replies.

  • Ivy Lopez-Oracle
    Ivy Lopez-Oracle Member, forum_netsuite_mod, ORACLE_EMPLOYEE Posts: 780 Bronze Crown

    Hi Daniel,

    I'm glad it works for you now! 😊

    If it's not too much trouble, we would greatly appreciate if you can click "Yes" on “Did this answer your question?” for the comment/s that helped in your concern. This will also help other community members who might have the same concern find the answer easier.

    Hope you have a great day!

    -Ivy

Answers

  • Emilia Ironov-Oracle
    Emilia Ironov-Oracle Member, forum_netsuite_mod, ORACLE_EMPLOYEE Posts: 703 Gold Trophy

    Hi Daniel,


    Welcome to NS Support Community and thank you for posting your concern here! 

    For new members, we suggest that you visit the NetSuite Support Community - Frequently Asked Questions (FAQs) for more information regarding the community. You can also update your profile and set your preferred username so other members can address you properly moving forward. 😊

    As per checking the ability to create KPI for Gross Margin % is still an existing enhancement in NetSuite. This is logged under Enhancement #73313.

    Alternate Solution:

    I. Create a Gross Margin Saved Search 

    -Lists > Search > Saved Searches > New 

    -Click Transactions 

    -Under Criteria tab, add the following: 

    Account Type is Income, COGS 

    Posting is True 

    -Under Results tab, add: 

    Field = Formula (Numeric) 

    Summary Type = Sum 

    Formula = NVL({creditamount},0)-NVL({debitamount},0) 

    -Under Available Periods tab, add:  

    Period 

    -Click Save 


    II. Add the Saved Search as Custom KPI 

    -Click the Set Up link on the KPI portlet 

    -Click Add Custom KPIs button 

    -Add the Gross Margin Saved Search 

    -Click Save


    This SuiteAnswer ID: 37870 can be helpful for you.


    To request or vote for product enhancement, please use SuiteIdeas page (SuiteAnswers ID: 30288) 

    To other users who may have insights with regard to this topic, feel free to add your comments by replying to this thread.  

     

    We apologize for this system limitation. 

     

    Kind regards,

    Emilia Ironov - Oracle | Ask a Guru

  • Daniel V
    Daniel V Member, forum_userscommunity_users Posts: 23 Newbie

    Thanks Emilia, although I can't seem to reconconcile the result to an income statement report?








  • Patrick Fresnosa-Oracle
    Patrick Fresnosa-Oracle Member, forum_netsuite_mod, ORACLE_EMPLOYEE Posts: 377 Bronze Trophy

    Thanks Emi!

    Hi @User_4KSCX,

    Thank you for the screenshots may I know what values or column in the custom Income Statement are you comparing?

    Here are the possible reasons as to why your Income Statement are not in line with the Save Search:

    1.) The custom Income Statement might not include all (Account Type) Cost of Goods Sold and all Income Account

    2.) In the criteria tab of your Saved Search, it seems that the Posting is not defined, kindly set it to true so that it can only capture the posting transactions

    3.) Since you have selected a date filter, it refers to date of the transaction and it is possible that your Income Statement is filtering out based on Accounting Period. To be able to have your reports based on the date of transaction you can do the following:

    A.) Customize the Income Statement Report and add a Date Filter (Customize Report > Go to Filters > Financial folder > Date) and select a date

    B.) You can also set the Report by period set to Never in order for your Financials to have a date filter.

    Note: When you select Financials Only or Never, you may encounter problems with data for KPI scorecards that use accounting periods. However, for the Custom KPIs the basis is based on the transaction date as this is still an enhancement under 382241 Home Dashboard > Key Performance Indicator > Custom KPI > Ability to select Period-Based Range and Compare Range (e.g. This Period, Last Period, etc.). Apologies for this system limitation

    4.) If you are using Oneworld, it is possible that some unwanted subsidiaries are included in either your Saved Search or your Custom Income Statement

    -------

    Here is how I was able to accomplish creating a custom KPI for the Gross Profit % in my test account:

    1.) I have created a Saved Search with the following criteria:

    (I only want in the KPI/Saved Search to show transactions for my Canada Subsidiary so I added a criteria)

    2.) Added the following in the Results tab:

    I added the Gross Profit % on the top of the results so that it will be looked up by the Custom KPI.

    The formula are as follows:

    Gross Profit%:

    SUM((CASE WHEN {accounttype}='Income' THEN {amount} ELSE 0 END)-(CASE WHEN {accounttype} = 'Cost of Goods Sold' THEN {amount} ELSE 0 END))/SUM(NULLIF((CASE WHEN {accounttype}='Income' THEN {amount} ELSE 0 END),0))

    Total Income Accounts (Optional)

    (CASE WHEN {accounttype}='Income' THEN {amount} ELSE 0 END)

    COGS (Optional)

    (CASE WHEN {accounttype} = 'Cost of Goods Sold' THEN {amount} ELSE 0 END)

    Gross Profit (Optional)

    3.) Also a date filter in the Available Filters (Optional for checking)

    (CASE WHEN {accounttype}='Income' THEN {amount} ELSE 0 END)-(CASE WHEN {accounttype} = 'Cost of Goods Sold' THEN {amount} ELSE 0 END)

    3.) I then added the Saved Search in the KPI Portlet

    a.) Navigated to Home

    b.) Set Up the KPI portlet

    c.) Add Custom KPI

    d.) Selected the Saved Search

    e.) Save

    Result:

    Hope this helps 😀

    ===========

    If you find this reply to your question as helpful, others with the same question might find it helpful as well. By marking “Yes” on the “Did this answer your question”, you’ll be able to aid the community to find the solution much easier and faster without the need to read through all the replies.

  • Daniel V
    Daniel V Member, forum_userscommunity_users Posts: 23 Newbie

    Thank you @Patrick Fresnosa-Oracle 😊 I have added the gross margin % to the Key Performance Indicators but I can't seem display compare periods and as a headline?



  • Ivy Lopez-Oracle
    Ivy Lopez-Oracle Member, forum_netsuite_mod, ORACLE_EMPLOYEE Posts: 780 Bronze Crown
    IPv6 Accepted Answer

    Hi @User_4KSCX,

    Kindly check your saved search if it has a Date filter in the Available Filters tab of the saved search.

    As mentioned in SuiteAnswers 8111, this is one of the requirements of the Saved Search in order to use it for comparison on a Custom KPI:

    • Not include any date fields defined as filters on the Criteria subtab
    • Have only one field with a summary type (such as group, sum, or count) defined on the Results subtab.
    • Have a date field defined as an available filter on the Available Filters subtab.

    Hope this helps! 😊

    -Ivy

    ===========

    If you find this reply to your question as helpful, others with the same question might find it helpful as well. By marking “Yes” on the “Did this answer your question”, you’ll be able to aid the community to find the solution much easier and faster without the need to read through all the replies.

  • Daniel V
    Daniel V Member, forum_userscommunity_users Posts: 23 Newbie

    That's working great @Ivy Lopez-Oracle. Thanks you.

    Last question, is it possible to add a custom KPI to a headline?






  • Ivy Lopez-Oracle
    Ivy Lopez-Oracle Member, forum_netsuite_mod, ORACLE_EMPLOYEE Posts: 780 Bronze Crown

    It is possible, but the Highlight If and Threshold columns must be populated. The result of the comparison of the custom KPI must meet the condition you set in those columns for it to be added as a headline in the portlet.

    For example, if you input:

    • Highlight If: Variance Greater Than
    • Threshold: 0

    Then it will be highlighted/shown as a headline if the variance is a positive number.

    Alternatively, you can also set Highlight If = Always so that it always shows as a headline.

    Hope this helps! 😊

    -Ivy

    ===========

    If you find this reply to your question as helpful, others with the same question might find it helpful as well. By marking “Yes” on the “Did this answer your question”, you’ll be able to aid the community to find the solution much easier and faster without the need to read through all the replies.

  • Daniel V
    Daniel V Member, forum_userscommunity_users Posts: 23 Newbie

    Thank you @Ivy Lopez-Oracle. That's great and works well.

  • Ivy Lopez-Oracle
    Ivy Lopez-Oracle Member, forum_netsuite_mod, ORACLE_EMPLOYEE Posts: 780 Bronze Crown

    Hi Daniel,

    I'm glad it works for you now! 😊

    If it's not too much trouble, we would greatly appreciate if you can click "Yes" on “Did this answer your question?” for the comment/s that helped in your concern. This will also help other community members who might have the same concern find the answer easier.

    Hope you have a great day!

    -Ivy

  • Daniel V
    Daniel V Member, forum_userscommunity_users Posts: 23 Newbie

    Hi @Ivy Lopez-Oracle and all,

    Is it possible to remove the decimals when displayed on the Dashboard Key Performance Indicator?


    Thanks,

    Daniel

  • Micah Timbol-Oracle
    Micah Timbol-Oracle Member, forum_netsuite_mod, ORACLE_EMPLOYEE Posts: 816 Silver Trophy

    Hello @Daniel V

    Have you tried using this formula? Formula: TO_CHAR({amount},'9,999,999') ?

    Micah Timbol - Oracle Ask A Guru

  • Daniel V
    Daniel V Member, forum_userscommunity_users Posts: 23 Newbie

    Hi @Micah Timbol-Oracle - do I add the formula in the criteria or results?






    or


  • Micah Timbol-Oracle
    Micah Timbol-Oracle Member, forum_netsuite_mod, ORACLE_EMPLOYEE Posts: 816 Silver Trophy

    On the Results tab > add Formula Text Formula: TO_CHAR({amount},'9,999,999')

    Click Save & Run

    😊

    Micah Timbol - Oracle Ask A Guru

  • Daniel V
    Daniel V Member, forum_userscommunity_users Posts: 23 Newbie

    @Micah Timbol-Oracle unfortunately, that doesn't seem to remove the two decimals?