Mapping Date Columns in Filters

Mapping Date Columns in Filters

Hi Team,

I created a chart where I am tracking USD in proposals per month by their sent date, won date, and projected close date. Those dates are all fields in the proposal record. On the x axis I am using sent date. On the y axis I have a sum_if formula to aggregate the USD for sent proposals, and 2 sum_ifs couched in map_groupbys for the won and projected proposals. The chart data is correct, but I need to be able to filter by the date. When I apply the sent date filter (since the x axis is per sent date), the map_groupby formulas exclude unintended data. For example, a proposal not yet won with sent date 1 Feb 2024 and projected date 1 Apr 2024 would have its USD aggregated in the sent column in Feb and the projected column in Apr. Without a filter, this is displayed correctly by the chart. However, when I apply a the sent date filter and try to look specifically at the month of Apr the aforementioned proposal is not included in the projected column. How can I create a filter that maps those other aggregates correctly? I do not see the option to map the columns when editing the filter. The overview scroll effect helps, but I want to default the view to the past 3 and next 3 months.

Formulas

Sent: sum_if("Proposals"."Proposal Stage" != 'Draft',"Proposals"."Grand Total")

Won: map_groupby(sum_if("Proposals"."Proposal Stage" LIKE '%Closed Won%',"Proposals"."Grand Total"),"Proposals"."Proposal Sent Date","Proposals"."Proposal Closed Won Date")

Projected: map_groupby(sum_if("Proposals"."Proposal Stage" NOT LIKE '%Closed%',("Proposals"."Grand Total"*(.01*"Proposals"."Win Probability"))),"Proposals"."Proposal Sent Date","Proposals"."Estimated Close Date")





    • Sticky Posts

    • Announcing Zoho Analytics 6.0 Beta!

      We are delighted to open up the next major version, Zoho Analytics 6.0 Beta! The new version comes packed with a wide range of functionalities for all persona, namely business users, data analysts, data engineers, and data scientists. Zoho Analytics team

    • What's New in Zoho Analytics - August 2024

      Hello Users! We are back with the latest updates and enhancements made to Zoho Analytics. Keep reading to learn more about them. Connect to the data hosted in the cloud without allow-listing the IP addresses Utilize Zoho Databridge to connect to the data

    • We are coming to your city! Zoho Analytics Community Meetup

      Hello, business leaders and data enthusiasts! We are delighted to announce that registrations are now open for the ZUG meetups, and we can't wait for you to be a part of them. Our in-house analytics experts are geared up to lead discussions on constructing

    • Zoho Analytics: 2021 Look Back

      As we start a new year in 2022, here's some of our top moments from 2021. Zoho Analytics in 2021

    • [Customer Talk] PREMO Group's Analyst Interview at Zoho Day 2022

      Premo Group, a 50 year old Spanish Manufacturing Company, has been our long-standing customer with #ZohoAnalytics. They've been using our platform for their end-to-end, unified business analytics solution.  Hear more from Claudio Cabeza, Director at PREMO

    • Recent Topics

    • Enhancements to Zoho Corp Help Center "Team Requests" View

      Dear Zoho Team, I hope this message finds you well. The ability to view both my tickets and my team’s tickets in the Zoho Corp Help Center is a fantastic feature, especially as the focal point for Zoho in our organization. However, we’ve encountered a

    • We've revised the pricing model of CRM portal user licenses

      Hello everyone, We’re making important updates to our pricing structure for portal user licenses, effective from the next payment cycle. The new slab-based pricing is as follows: Previously, these portal user licenses were priced at $5/ user/ month. As

    • Zoho and Hostaway webhok integration.

      I want to receive data coming from hostaway webhook and receive it in zoho crm to create or update record in a module based on conditions. The hostaway webhook sends data every time a reservation is created or modified or cancelled. The hostaway sends

    • Layout Prompt when creating an oportunity

      Hi There, Is there a way to create a pop-up prompt when I create a new Deal giving me the option of what layout to use based on a certain requirement? So, if the prompt had two options 1. is a new business Deal 2. is a renewal Deal Thanks in advance

    • A recap of Zoho Sprints 2024

    • Unable to create custom fields for shipment order

      I'm unable to create custom fields for shipment orders, even though the custom fields are set up correctly. A request to the following endpoint: https://www.zohoapis.com/inventory/v1/settings/preferences/customfields?organization_id=${ZOHO_ORGANIZATION_ID}&entity=shipment_order

    • Records per page in New UI

      It seems the new UI lack of "Records per page" function, it is very handy if you are looking for a data that you don't know the exact search term, but you know it may "between" few entries. without a "page" function, we kind of have to keep page down and page, the autoload is not that fast, and you are dealing with thousands of entries. Could we please have the "records per page" function back to New UI (also it shows total counts of the record) Looking forward to hear from you.

    • zet pack not working

      We are using the zet pack command to package our Zoho extension. However, after running the command, the extension gets packed, but the resulting package is empty. We've attached a screenshot for reference. Could you please assist us with resolving this

    • CSV File Added to Form - Parse and Map to Fields

      Hi,  I apologize, I can't seem to find a clear explanation or help article on how to parse a CSV file. On validate, I process this deluge script. fileContent = Collection(input.File_upload.content); result = fileContent.values(); info result; What I want to do is create a record (in another form), and map certain fields to fields in the form.  I can get values, which is the whole file but no keys. If I use result = fileContent.keys(); I get "0" As you can see from CSV, my first line is my map and

    • Encountered an error while creating a bill in Zoho Inventory: {"code":6,"message":"Invalid data provided"}

      I attempted to create a bill using the Zoho Inventory API, but I received an error: {"code":6,"message":"Invalid data provided"}. However, when I made the same request again, it was successful. Does anyone have insights on why this happened?

    • Free user licenses across all Portal user types

      Greetings everyone, We're here with some exciting and extensive changes to the availability of free user licenses in CRM Portals. This update provides users with access to all Portal user types for free to help them diversify their user licenses and explore

    • Custom "Filter By" in Client Portal

      Currently our client portal only shows items for that specific person that is logged on to the portal, we want the current logged user to see all items for that user's company. An example would be invoices, so the current user would see all invoices for

    • Why do I need to send the Customer ID in the Create Purchase Order Request?

      I'm trying to create a purchase order using this endpoint https://www.zoho.com/inventory/api/v1/purchaseorders/#create-a-purchase-order Unfortunately, I'm getting this error { "code": 4, "message": "Invalid value passed for Customer ID" } The doc doesn't

    • How to Retrieve Serial Numbers of Items in Zoho Inventory via API?

      Hello, I am currently working with the Zoho Inventory API and need to retrieve the serial numbers associated with specific items in our inventory. After reviewing the documentation, I couldn’t find an endpoint dedicated to fetching serial numbers for

    • merging email accounts

      previously I was using 5 mail pop mailboxes within VO , 2 of them are becoming obsolete so I was thinking about deleting the obsolete ones and merge the remainders into my main account mailbox within VO , is this possible ? thnx in advance.

    • Queries filtered by current page/record

      I have been trying to use the new queries feature, and I can filter the query, but I'm coming unstuck because I don't understand how to make the query dynamically include the filter of the current record. ie if I'm on a deal, to filter all the records

    • ZohoPeople API - Retrieve leave type IDs

      Hi All, I have created a leave type in Zoho People UI. Now I need to fetch the  Leave Type ID of it. As per the documentation[1] I used the  curl request [2]. But I ended up with the error response from the API.  {"response":{"message":"Error occurred","errors":{"message":"Server Error Occured","code":7031},"status":1,"uri":"/api/leave/getLeaveTypes"}} The new API[3] does not tell anything on how to retrieve the Leave Type ID.  Have you done any changes to the API recently. If so please let me know

    • Inadequate Customer Support

      Hello & Greetings! I have been a pro Zoho user since the last 2 years and I would admit that the apps that are being offered are good, however the support we receive has a lot more to achieve. This being a design issue rather than a staff issue. Being

    • Incorrect Closing Stock Amount value

      Act as Zoho Inventory Expert. We are a construction company, OVAL Projects Engineering Limited. We started using Zoho Inventory for Stock Management.I have multiple warehouses. I have encountered a problem while generating custom warehouse wise inventory

    • The Next Chapter for CRM for Everyone: Moving from Early Access to Phased Rollout for Customers

      #CRM25Q1 Hello Everyone, Until now, CRM for Everyone has been available in early access mode exclusively for users who opted to try the new version. We are now transitioning to a phased release, starting with the basic edition. We are thrilled to announce

    • Zoho vault uses only password to unlock not a TouchID

      TouchID works when normally openning app but when called from keyboard while browsing or trying to log to another app it shows only password option to unlock. This behaviour is only on iPad Mini witch latest 18.2OS

    • Unable to add Guest Members

      We are having issues adding Guest Members to our Cliq account. We have sent out a number of request but it seems that only some people are able to access the platform. Others have received a message stating that they need to be granted access from an

    • Webhook when estimate is refused is not firing

      Hello, I use a workflow through make that sends estimate with zoho books (I paid books and sign). -Those estimates when accepted are firing the webhook that I create in zoho sign (photo 1) -However when refused they are not firing the webhook that I created

    • New Leave Type: Compensatory off

      Hi, there is a new Leave Type: Compensatory off. Can someone tell me how to use it, because it sounds it could work for overtime compensation for our techs. Thanks Andreas

    • Invoice status on write-off is "Paid" - how do I change this to "Written off"

      HI guys, I want to write off a couple of outstanding invoices, but when I do this, the status of the invoices shows as "Paid". Clearly this is not the case and I need to be able to see that they are written off in the customer's history. Is there a way

    • Create a custom button to modify custom fields in zoho Inventory

      I am needing a script for two buttons, 1. Button will add todays date to a custom field named cf_sent_to_sov 2. Button will mark a checkbox or unmark a checkbox field named cf_parts_ordered I have been trying to figure out deluge but have not got anywhere

    • How to add a record for a different report

      I have one form and it has two reports I need to programmatically add records to both reports For example one report is draft and other is processed After the user performs some action on the draft report I want to create a new report in Processed and

    • Create custom rollup summary fields in Zoho CRM

      Hello everyone, In Zoho CRM, rollup summary fields have been essential tools for summarizing data across related records and enabling users to gain quick insights without having to jump across modules. Previously, only predefined summary functions were

    • How do I fix this? Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked.

      How do I fix this? Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked.

    • Webhook 'when estimate is refused' is not firing

      Hello, I use a workflow through make that sends estimate with zoho books (I paid books and sign). -Those estimates when accepted are firing the webhook that I create in zoho sign (photo 1) -However when refused they are not firing the webhook that I created

    • Amazon Integration

      Hi, I am seller on Amazon , & I would like to sign up for Zoho books. However my question is can we automate/integrate invoicing, charges and returns in amazon with Zoho using API? Do you have a developer for this? I did take a look at zapier however it just has a create Invoice function nothing else.

    • Link project tasks to tasks in CRM and/or other modules.

      Hello, I have created and configured a project in Zoho Projects with a set of tasks. I would now like to link these tasks (I imagine according to the ID of each one) to actions in the CRM: meetings, tasks, analytics). The aim is to link project tasks

    • Zoho CRM API Credits & Limits for Workflow

      Hi Team, Just wanted to clarify how the API credits work for Zoho CRM and workflows with custom functions. API Credits are based on your subscription and are set at the account level. You can buy additional credits if needed. For Enterprise customers,

    • Count the NUMBER of Contacts for an Account automatically

      Hello. Is there any way Zoho can count the number of CONTACTS for a particular ACCOUNT and have a field in the ACCOUNT module update itself automatically? Currently we use Zoho to administer our language school and the Contacts represent students and Accounts represent Grupos (Classes). It would be very useful for us to have a feature like this enabled, and I can see other similar applications requiring something like this. The solution would be even better if the Contacts met a specified criteria,

    • How to use Twilio to send appointment notification and reminder SMS in Zoho Bookings

      Hit no-shows out of the ballpark by combining Zoho Bookings and SMS providers. SMS notifications help you remind customers of their appointments and reduce no-shows by reaching out where they are. In this guide, we'll configure an SMS provider called

    • geographic search filter in map view

      Hi, I have a recruiting and timesheet system built in Creator. The client wants to enhance the search for candidates based on their location and filter by job skills - currently they look on the Map View which uses the geo location or post code of the

    • Announcement: Upcoming changes to the permission grant flow for OAuth apps

      This announcement is intended for app developers who use the Zoho API console. We're going to implement an important update to the way users grant permission for the OAuth apps created through the API console. What’s changing? Currently, users can grant

    • Add Google Workspace Module to Zoho Flow

      Dear Zoho Flow Team, I hope this message finds you well. We’d like to request the addition of a dedicated Google Workspace module in Zoho Flow. Currently, there are no triggers or actions for Google Workspace, which limits our ability to integrate and

    • How do i remove the Powered by Zoho logo from my careers page

      Can I remove this? ​

    • Totals on Pivot Table

      Is there a way to change the way the subtotal calculates? In this example I have a formula to give me the average monthly attendance ....but I want the grand total of the month to be the sum of all the average attendances...any ideas? Thank you!@

    • Next Page