Red Atok-Oracle

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

in SuiteAnalytics Workbook is showing the incorrect quarter Comment by Red Atok-Oracle Oct 25, 2022 3:08PM

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…

in SuiteAnalytics Workbook is showing the incorrect quarter Comment by Red Atok-Oracle Oct 24, 2022 2:26PM

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…

in SuiteAnalytics Dataset Group By Comment by Red Atok-Oracle Jun 10, 2021 6:41PM

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…

in Summarized Saved Search Formula Field Skews data Comment by Red Atok-Oracle Jun 2, 2021 3:27PM

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…

in Summarized Saved Search Formula Field Skews data Comment by Red Atok-Oracle Apr 14, 2021 8:20PM

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…

in Transaction Saved Search Summary That Summarises the Name Field from Journals Lines Comment by Red Atok-Oracle Apr 14, 2021 6:22PM

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.

in Transaction Saved Search Summary That Summarises the Name Field from Journals Lines Comment by Red Atok-Oracle Apr 10, 2021 1:58PM

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

in Transaction Saved Search Summary That Summarises the Name Field from Journals Lines Comment by Red Atok-Oracle Apr 3, 2021 9:32PM

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

in Formula To Return Employee Name If Project Role End Date Is In the Future Comment by Red Atok-Oracle Apr 1, 2021 11:30AM

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…

in Transaction Saved Search Summary That Summarises the Name Field from Journals Lines Comment by Red Atok-Oracle Apr 1, 2021 7:41AM

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…

in Formula To Return Employee Name If Project Role End Date Is In the Future Comment by Red Atok-Oracle Apr 1, 2021 7:24AM

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

in Formula To Return Employee Name If Project Role End Date Is In the Future Comment by Red Atok-Oracle Mar 25, 2021 8:26PM

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

in WEBINAR Q&A: Can we use the "escape '<char>'" to search for strings... Comment by Red Atok-Oracle Mar 25, 2021 8:12PM

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…

in Saved Search Case Formula That References A Date to Last Week Comment by Red Atok-Oracle Feb 24, 2021 8:58PM

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

in Saved Search Case Formula That References A Date to Last Week Comment by Red Atok-Oracle Feb 23, 2021 8:46PM

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…

in Saved Search Case Formula That References A Date to Last Week Comment by Red Atok-Oracle Feb 23, 2021 8:38PM

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…

in What is and is not inline editable from a Saved Search? Comment by Red Atok-Oracle Feb 4, 2021 1:33PM

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…

in What is and is not inline editable from a Saved Search? Comment by Red Atok-Oracle Feb 3, 2021 7:29AM

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

in Formula to Extract String from Text Set Comment by Red Atok-Oracle Jan 7, 2021 2:06PM

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…

in Report on daily sales orders and cash sales Comment by Red Atok-Oracle Dec 27, 2020 10:57PM

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'…

in Entity Table Save Search - surface Entity Type Comment by Red Atok-Oracle Dec 16, 2020 9:58PM

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…

in SQL waaaaay beyond my ability for date range Comment by Red Atok-Oracle Dec 11, 2020 5:24PM

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

in Analytics Yearly Overlay Comment by Red Atok-Oracle Dec 11, 2020 11:24AM

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…

in Analytics Yearly Overlay Comment by Red Atok-Oracle Dec 10, 2020 9:18PM

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?

in SQL waaaaay beyond my ability for date range Comment by Red Atok-Oracle Dec 10, 2020 8:33PM

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?

in Analytics Yearly Overlay Comment by Red Atok-Oracle Dec 10, 2020 8:31PM

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

in Analytics Yearly Overlay Comment by Red Atok-Oracle Dec 10, 2020 4:56PM

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…

in SQL waaaaay beyond my ability for date range Comment by Red Atok-Oracle Dec 10, 2020 4:46PM

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…

in Analytics Yearly Overlay Comment by Red Atok-Oracle Dec 10, 2020 1:53PM

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…

in SQL waaaaay beyond my ability for date range Comment by Red Atok-Oracle Dec 10, 2020 11:18AM

MFA for Oracle Community

Red Atok-Oracle Bronze Medal

Comments