Red Atok-Oracle Bronze Medal

Kindly try: CASE WHEN EXTRACT(Month FROM {yourdatefield}) IN (7, 8, 9) THEN 'Q1' WHEN EXTRACT(Month FROM {yourdatefield}) IN (10, 11, 12) THEN 'Q2' WHEN EXTRACT(Month FROM {yourdatefield}) IN (1, 2, 3) THEN 'Q3' WHEN EXTRACT(Month FROM {yourdatefield}) IN (4, 5, 6) THEN 'Q4' END

Hi Lea, I think workbook uses typical quarter like Jan - Mar as Q1 and so on. As a workaround, you can try to create a formula for your date field. See sample below: CASE EXTRACT(Month FROM {yourdatefield}) WHEN 4 THEN 'Q4' WHEN 5 THEN 'Q4' WHEN 6 THEN 'Q4' WHEN 7 THEN 'Q1' WHEN 8 THEN 'Q1' WHEN 9 THEN 'Q1' WHEN 10 THEN…

Hello @Jeroen Sprangers ZS Welcome to our Community! I reviewed your inquiry about SuiteAnalytics workbook. Apparently, Dataset could only contain the fields and formula details, but it does not have the ability to group such rows. The ability to summarize/aggregate the results can be utilized in a workbook, using a Pivot…

Hi @Tafara Sorry for the late revert. It has been long since I checked my messages here. I see that you are having discrepancies on your 'CEO Project Placement Resource Count' search. Based on your Criteria, it seems that you are counting from your custom Resource field, per Client and End Client values? Does this mean you…

Hi @Tafara , Red here again ? I see that you are inquiring for your custom fields count. Based on your screenshots, it is grouped using COUNT. What do you mean by having wrong results? Is it different from what you are expecting? Kindly you elaborate more on these numbers? Have you tried counting using integers on your…

Hi @Tafara , Is it safe to assume that the -None- values show up for Bill transactions only? Looking at your fields, it seems that you are using Amount (Credit) and Amount (Debit). I think it is expected that there is no Name value for at least of these amounts fields. You can open one Bill transaction and open the GL…

Hi @Tafara , Sure, let us start over.. 1. Please send me the screenshot of the result of the grouping when type is Bill only. 2. Then, kindly send me the screenshot of the result of the grouping when type is Journal only. 3. Do you see any similarities with the data with the =None= values? Please advise.

To confirm, since the NULL values have gone, do you still need further assistance for this? Were you able to identify the transaction type which produces the Null values? Let me know if you have further questions.. @Tafara

Hi @Tafara Your formula looks valid, I tried it on a Project search and it is working fine. If you are in a transaction search, try to convert it as: DECODE({accounttype}, 'Income', {amount}, 0.0) Thanks, Red

Hello @Tafara, We need more information to further advise for this. Just to confirm, is the Supplier column coming from the Formula (text) field, or just the Name field? Have you tried removing the Grouping for Formula Text field? How does it show? Can you also try to add the Type field on the Results to see which type…

You're welcome @Tafara .. You can try to remove/add joined fields on your Project search (if the formula is working fine with Project Tasks) See sample below: Try the {projecttask.custevent_hw_role_end_date} without projecttask like: {custevent_hw_role_end_date} only. Therefore, use it as: CASE WHEN…

Hi @Tafara , You can try and play around with this formula below: CASE WHEN {startdate} < {today} AND {enddate} > {today} THEN 'ACTIVE' ELSE 'Inactive' END Please Note: Replace the {startdate} and {enddate} with the ID of your Start and End Date fields. Hope this helps ? Thanks, Red

Hi Ludek, @3437516 Based on my test, YES we need to add escape characters when searching for wildcards: % and _. Please see sample formula below: For Underscore: CASE WHEN {name} LIKE '%\_%' ESCAPE '\' THEN 1 ELSE 0 END For Percent: CASE WHEN {name} LIKE '%\%%' ESCAPE '\' THEN 1 ELSE 0 END Hope this helps ? Thanks, Red

Yes, apologies for the confusions. You may disregard the first solution using the TO_CHAR, as the second solution TRUNC would be more suitable for your requirements. The solution: Case When TRUNC({today},'IW') - TRUNC({custevent_hw_role_start_date},'IW') = 1 Then 1 Else 0 End, should cover other years as well and you need…

Hi @Tafara , Just an update: You may also use this as Criteria to cover other years as well: Field = Formula (Numeric) Formula = Case When TRUNC({today},'IW') - TRUNC({custevent_hw_role_start_date},'IW') = 1 Then 1 Else 0 End Set: Value = 1 Please advise. Thanks, Red

Hi @Tafara , Glad to have you with us. Can you try to add this to your Criteria: Field = Formula (Numeric) Formula = Case When TO_CHAR(SYSDATE,'IW') - TO_CHAR({custevent_hw_role_start_date},'IW') = 1 Then 1 Else 0 End Set: Value = 1 This should pull all project task which has Start Date equal to last week. Note: By last…

Hi @Pierre_Plourde , Glad to have you with us. I agree, most of the standard date fields on transactions are not available for Inline Editing. We have two outstanding enhancements which request this ability: Enhancement# 206575 : Inline Editing = True > Transactions > Sales > Enter Sales Orders > List: Ship Date is not…

Hi @NetSuite4All , Glad to have you in this community. We currently have no outstanding list of the transaction types which do not support Inline Editing. However, we have this article published: SuiteAnswer ID# 11844 - Inability to Edit a Field when Inline Editing is Enabled in a List or a Saved Search, which explains the…

Hi @hunter , Can you try: RTRIM(REGEXP_SUBSTR({id_of_your_text_field}, '[^:]+',6, 7), 'Position') Note: please replace the {id_of_your_text_field} to the internal ID field of your Text Message field. Please keep us posted. Thanks, Red

Hi @JoshPNW , Glad to have you in this community. You can create a saved search to pull all the sales order and cash sales transactions in your account. Please refer to the steps below: a. Navigate to Reports > New Search b. Click Transaction c. Click Create Saved Search button d. Add Search Title: e.g. 'Sales Order and…

Hi @KNason76 , The entity type field may not be an exposed field when doing an entity search. However, you may try to use the System Notes.. Old Value field and create a formula from it. You may try to add this formula field on your entity search for your test: Formula (Text) = Case when {systemnotes.oldvalue} = 'CustJob'…

Hi Chris, The T-SQL reference you provided is actually similar to the previous code provided. As you notice, the 'D' = '7' checks if the date is Saturday, then add 2 on the number of days. While the 'D' = '1' checks if the date falls on Sunday, then add 1. However, this is not applicable on your case. We need to loop your…

Glad it worked for you :) Yep, its sad that it is not utilizing the stacked features though.

Can we try to create 2 formula fields on the dataset? TO_CHAR({enddate},'Month') TO_CHAR({enddate},'YYYY') Then on the Chart layout, choose 'Stacked Column Chart' For X-axis, chose the TO_CHAR({enddate},'Month') formula field For Color Stack, TO_CHAR({enddate},'YYYY') formula field Measures: Line Total (Sum) Let me know…

I do not think it is possible since your N is undefined. I am unable to compute the number of weekends between dates (to at least add to the results) but we have no reference of the end date since N is constantly changing. How is your N computed by the way? Maybe we can check from there?

The sequence of the X axis is in alphabetical order, which is why it is better to show the year first, before the month value. TO_CHAR({trandate},'YYYY - MM - Month') What do you mean by not stacking the years, can you provide me a sample?

How about adding the year number and month number on the formula? TO_CHAR({trandate},'YYYY - MM - Month') Would this work for you? Thanks, Red

Hi Chris, The formula provided should be able to cater the working days requirements. If you need to adjust N to 6, it should be: CASE WHEN (to_char(LAST_DAY({today}) + 6,'D') = '7') THEN LAST_DAY({today}) + 6 + 2 WHEN (to_char(LAST_DAY({today}) + 6,'D') = '1') THEN LAST_DAY({today}) + 6 + 1 ELSE LAST_DAY({today}) + 6 END…

Hi @User_HPB3I , Welcome to this community. Can you verify which visualization type were you using, can you give us a screenshot of your current setup? When choosing the date column, do you have the option to assign Date Hierarchy = Year? Also for your other question, you can add a formula on the DataSet and use at as your…

Hi @HertsChris , Welcome to this community. I am not exactly sure how your N is computed, but if you only need a simple formula, you may start with this: Formula (Numeric) = CASE WHEN (to_char(LAST_DAY({today}) + N,'D') = '7') THEN LAST_DAY({today}) + N + 2 WHEN (to_char(LAST_DAY({today}) + N,'D') = '1') THEN…