Dynamic Date Filtering for KPI Widgets – Need Help with Query Table

Dynamic Date Filtering for KPI Widgets – Need Help with Query Table

Hello,
I'm seeking help with setting up dynamic date filtering for my KPI widgets in Zoho Analytics. Below is an overview of my setup and the issues I'm facing:

Why:
I need my KPI widgets to update dynamically based on a date range selected via a dashboard date picker. My goal is to have the entire data chain—from the original source table, through my Query Tables, to the KPI widgets—update automatically when the date range changes.

How:

Original Table Presentation:
    1. My initial data is stored in a wide-format table where each dossier (record) includes separate date columns for different process stages. For example:

      IndexColContactDateMandatDateAccordClientDateSoldeDate
      12024-01-052024-01-092024-01-152024-01-20
      22024-02-032024-02-082024-02-122024-02-20
      ...............


Unpivoted Table ("Unpivoted_CRMKeyed"):

To simplify analysis, I transformed the wide table into a long format using a Query Table, resulting in:

IndexColEtapeDateEtape
1
Contact2024-01-05
1Mandat2024-01-09
1Accord Client2024-01-15
1Solde2024-01-20
2Contact2024-02-03
2Mandat2024-02-08
.........

This unpivoted structure allows me to easily calculate delays between stages and apply a single global date filter on "DateEtape."

Query Tables and KPI Widgets:

First Query Table:
I created a Query Table grouping by IndexCol to calculate delays between stages. For example, to compute the days between "Contact" and "Mandat":

  1. SELECT
  2.     "IndexCol",
  3.     DATEDIFF(
  4.         MAX(CASE WHEN "Etape" = 'Mandat' THEN "DateEtape" END),
  5.         MAX(CASE WHEN "Etape" = 'Contact' THEN "DateEtape" END)
  6.     ) AS "DiffContactMandat",
  7.     -- Additional calculations for other stage delays...
  8. FROM "Unpivoted_CRMKeyed"
  9. WHERE "DateEtape" >= '2024-01-01'
  10.   AND "DateEtape" <= '2024-12-31'
  11. GROUP BY "IndexCol"
This Query Table works perfectly with static dates.

Second Query Table & KPI Widgets:
I then built a second Query Table to aggregate the delay calculations (for example, computing the average delay for each transition) and used these aggregated values as the source for my KPI widgets.

  1. SELECT
  2.     AVG("DiffContactMandat") AS "Moy_ContactMandat",
  3.     AVG("DiffMandatAccord_Client") AS "Moy_MandatAccord_Client",
  4.     AVG("DiffAccord_ClientSolde") AS "Moy_Accord_ClientSolde",
  5.     AVG("DiffContactSolde") AS "Moy_ContactSolde"
  6. FROM "FirstQueryTable"

Sample Result of Second Query Table:

Moy_ContactMandat
Moy_MandatAccord_Client
Moy_Accord_ClientSolde
Moy_ContactSolde
4.8
6.2
7.1
12.3

I then use these aggregated values as the source for my KPI widgets.

Dynamic Date Filtering Issue:
I want to replace the static date values in the Query Tables with dynamic ones controlled by a dashboard date picker (using user variables like ${varDateStart} and ${varDateEnd}). However, when I attempt to do so, I get errors (e.g., "Invalid Date Format" or "Usage of variables in SQL query is not supported"). I've verified that the variables contain strings in "YYYY-MM-DD" format, but the dynamic filtering still fails.

I need guidance or workarounds to implement dynamic date filtering for my KPI widgets in Zoho Analytics. Specifically:

How can I enable a dashboard date picker to update user variables (or otherwise apply dynamic date filters)?
How can I apply these dynamic dates to filter either the original table or the Query Tables, so that the entire KPI chain updates automatically?
Are there any recommended alternative approaches if user variables cannot be used directly in Query Tables?

Any help or suggestions to resolve this issue would be greatly appreciated.

Thanks in advance!

Best regards,

Christophe

      Zoho Campaigns Resources


        • Desk Community Learning Series


        • Digest


        • Functions


        • Meetups


        • Kbase


        • Resources


        • Glossary


        • Desk Marketplace


        • MVP Corner


        • Word of the Day


        • Ask the Experts


          • Sticky Posts

          • Geo-Powered Retail Intelligence with Zoho Analytics

            In today’s highly competitive retail landscape, data-driven decisions are no longer optional — they’re essential. While businesses collect vast volumes of data across regions, stores, and customer segments, the real value lies in how effectively this
          • Create and save user-specific filter views with Zoho Sheet

            The filters in Zoho Sheet have become even more collaboration-friendly. Previously, whenever you filtered any data, the filtered view will be updated for all the spreadsheet collaborators. This disturbed the data for others working on the same file. With this new update, you can apply data filters without altering the view for other collaborators in the file.   Collaborate with user specific filters   Let us say you are working on a sales report spreadsheet in real time with two remote colleagues. If
          • Customer Segmentation using RFM Analysis

            How well do you know your customers? Whether you operate in a B2B or B2C space, chances are that 80% of your business comes from just 20% of your customers (Pareto's Principle). According to a study by Forbes, acquiring new customers costs five times
          • 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

          Zoho CRM Plus Resources

            Zoho Books Resources


              Zoho Subscriptions Resources

                Zoho Projects Resources


                  Zoho Sprints Resources


                    Zoho Orchestly Resources


                      Zoho Creator Resources


                        Zoho WorkDrive Resources



                          Zoho CRM Resources

                          • CRM Community Learning Series

                            CRM Community Learning Series


                          • Tips

                            Tips

                          • Functions

                            Functions

                          • Meetups

                            Meetups

                          • Kbase

                            Kbase

                          • Resources

                            Resources

                          • Digest

                            Digest

                          • CRM Marketplace

                            CRM Marketplace

                          • MVP Corner

                            MVP Corner




                            Zoho Writer Writer

                            Get Started. Write Away!

                            Writer is a powerful online word processor, designed for collaborative work.

                              Zoho CRM コンテンツ










                                ご検討中の方

                                  • Recent Topics

                                  • Trouble fetching custom fields from Zoho Booking

                                    I'm following the instructions from here : https://help.zoho.com/portal/en/kb/flow/user-guide/app-specific-documentation/articles/zoho-bookings#1_Problem_with_accessing_custom_fields But I can't get this to work. Here is the result of a test booking :
                                  • Missing Date Field Type Blocking VAT Calculations in Custom Zoho CRM Module

                                    I'm working on a custom VAT calculator within Zoho CRM where I need to store the purchase date of a product and calculate VAT based on that date, since VAT rates can vary over time. However, while creating custom fields, the only available data type is
                                  • Different Page Numbering

                                    I want the page numbers not just 1,2,3... but i,ii,iii,iv,... or even I,II,III,IV.... How can I do this?
                                  • Remote Control Functionality During Screen Sharing in Zoho Cliq

                                    Hello Zoho Cliq Team, We would like to request the addition of remote control functionality during screen sharing sessions in Zoho Cliq. Currently, while screen sharing in Cliq is very useful, it lacks the ability for another participant to take control
                                  • Real-Time Screen Annotation During Zoho Cliq Screen Sharing

                                    Hi Zoho Support Team, Hope you're doing well. We’d like to request the addition of real-time screen annotation tools during screen sharing sessions in Zoho Cliq video calls. 🔍 What We're Looking For: The ability for the presenter—and optionally, other
                                  • Simplify ticket replies with intelligent writing and content analysis tools

                                    Introducing a smarter, faster way to handle ticket responses with generative AI-powered capabilities, now available in the ticket detail view. These enhancements are designed to reduce agent effort, improve clarity, and elevate the overall support experience.
                                  • Zoho Books - Feature Request - Show Custom Template Preview

                                    When using a custom template is would be a great user experience if the custom template could be previewed when viewing the record. I have found that it's very confusing for users, when they see an invoice preview but the out put file is different. Additional
                                  • run a macro on a contact that bounced from crm email merge

                                    how would one run a macro on a contact that bounced from crm email merge? how would i tell zoho to run a macro that SIGNALS said bounced?
                                  • Workflow : Update a multiline text field

                                    Hello, I'm creating a workflow to update a multiline text field. But it looks like I only can format my text as a one-line. How can I format the text to multiple lines ? Thanks
                                  • Search handwriting using sketch card and OCR

                                    Hi It's possible using Sketch Card for handwriting and search them using AI and OCR in Pro edition? Thanks
                                  • Autoresponders in Zoho CRM will be discontinued—transition to Cadences for enhanced engagement

                                    Zoho CRM’s Autoresponder feature will be discontinued by September 30, 2025. If you're currently using Autoresponders to automate email follow-ups, we recommend switching to Cadences, a more powerful, flexible, and multi-channel engagement tool for today's
                                  • Recovering a note

                                    Hi, I accidentally deleted an important academic not from my notebook. Can I recover it? Thanks
                                  • Unable to schedule posts!

                                    Hi everyone, I'm on the free account. I just realised it doesn't give me the options to schedule posts anymore, I can only 'post now'. I don't understand why I can't even see what I scheduled before. Can anyone help? Thanks, Benedetta
                                  • ZUG is Hitting the Road — Across the USA!

                                    We’re bringing the Zoho User Group (ZUG) meet-ups back to various cities across the United States — and we’re more excited than ever to reconnect with our incredible community! Whether you're a seasoned Zoho user or just getting started, this event is
                                  • SPF, Zoho Books, Send from my domain

                                    I am unable to verify my domain through Zoho Books: this is the text record: v=spf1 include:spf.protection.outlook.com include:zohomail.com -all I waited 24 hours already with error: SPF record not found. Contact your domain provider.
                                  • How do I record timesheet invoices generated in Zoho Workerly against a Sales Order?

                                    We have customers who issue us a Purchase Order for an aggregate amount of hourly services, which we invoice against on a weekly, bi-weekly, or monthly basis (contract dependent). For simplicity, let's say the customer PO is for $50,000 (1,000 hours at
                                  • One Place for All Your Automation Needs

                                    All automation settings are grouped under Settings ()> Automation. This helps you find everything related to automation from one place. Under Workflow Rules, Email Alerts, Email Templates, and Webhooks: Use the Projects tab for project-specific settings.
                                  • How Do I Refund a Customer Directly to Their Credit Card?

                                    Hi, I use books to auto-charge my customers credit card. But when I create a credit note there doesn't seem to be a way to directly refund the amount back to their credit card. Is the only way to refund a credit note by doing it "offline" - or manually-
                                  • header and footer for templates

                                    Hi,   I created many templates for my activity. Nevertheless I have a big problem, the HTML is varies from a quote to another, there is sometime less or more text. In order to have a nice layout I require Header and Footer. I looked for into the CRM tool, couldn't find it. Can someone explain to me if it's possible ??   It's very urgent, thanks very much,   Eric Marois
                                  • Simple Text Search Function

                                    Would it be too much to ask for a simple text search function? My slide decks are often simply collections of slides of a random over, and I often have to find the slide I need at a moment's notice. A text search function, no matter how rudimentary, would
                                  • Rich-text fields in Zoho CRM

                                    Hello everyone, We're thrilled to announce an important enhancement that will significantly enhance the readability and formatting capabilities of your information: rich text options for multi-line fields. With this update, you can now enjoy a more versatile
                                  • Introducing the Reviews sub-module in Zoho Recruit

                                    Across every recruitment process, candidates are assessed by multiple stakeholders—recruiters, interviewers, hiring managers, and clients. These evaluations influence hiring decisions, yet they often exist in silos across assessments, emails, or interview
                                  • We need customizable sub-form layouts

                                    Currently, we can arrange sub-form fields only in a single row. The single row layout means salespeople must horizontally scroll to uncover information. As a result, salespeople cannot see all of the relevant information simultaneously.  The administrator
                                  • Fetch function not working for CRM Contacts

                                    I've created a flow that checks if a contact exists in CRM (based on form input), and if it does, then it updates one of the fields for the contact. In my test, the fetch function correctly identifies that a contact exists (based on email address), but
                                  • How to add a discount (percent %) field?

                                    My particular case it’s regarding the “Opportunities” module. I’ve created a subform to calculate the value of the opportunity, and now I’d like to include a discount field in the form of a percentage. However, no matter how or where I add the "percent"
                                  • Automate Pricebook per Customer

                                    Example Scenario: I want to create a customer package (Silver Package, Gold Package, Platinum Package) and associated it with a Price Book that contains discounted prices for certain products. When a customer assigned to this Silver Package places an
                                  • Automate pricebook per customer

                                    Example Scenario: I want to create a customer package (Silver Package, Gold Package, Platinum Package) and associated it with a Price Book that contains discounted prices for certain products. When a customer assigned to this Silver Package places an
                                  • Automate pricebook per customer

                                    Example Scenario: I want to create a customer package (Silver Package, Gold Package, Platinum Package) and associated it with a Price Book that contains discounted prices for certain products. When a customer assigned to this Silver Package places an
                                  • Client Side Scripts for Meetings Module

                                    Will zoho please add client side scripting support to the meetings module? Our workflow requires most meeting details have a specific format to work with other software we have. So we rely on a custom function to auto fill certain things. We currently
                                  • Document Tracking

                                    Hi all Zoho crmplus user here. I am migrating from hubspot and in Hubspot when my sales team email a document they can see when the client has opened the document and how many minutes they sepend on it. This persists for additional openings and readings
                                  • Apply partial payments to invoices from the Banking Module

                                    We need this! Why is this not possible?
                                  • Forecast UI improvements

                                    Hi I have two improvement requests for the UI in the Forecast function. Can you add the ability to reorganise the Pipeline, Committed and Best Case columns on the Forecast? I thought they were in alphabetical order, so we renamed them so that we could
                                  • Missing services/ features / buttons

                                    Hello, 1. I can't locate the DKIM information in ZoHo CRM that I need to add as txt strings to our DNS in GoDaddy. 2. In other activities I cannot locate the 'Help' button for context senstivie help 3. I wish to merge two records into a single record
                                  • Global Sets for Multi-Select pick lists

                                    When is this feature coming to Zoho CRM? It would be very useful now we have got used to having it for the normal pick lists.
                                  • [Free Webinar] Building Data Relationships Using Lookup Fields - Creator Tech Connect

                                    Hello Everyone! We welcome you all to the upcoming free webinar on the Creator Tech Connect Series. The Creator Tech Connect series is a free monthly webinar that runs for around 45 minutes. It comprises technical sessions in which we delve deep into
                                  • Staff Tracking in

                                    Hi , I would like to see what activity my staff does over Zoho CRM and over Zoho Mail . I need to know which deals in CRM haven't been touched or had an activity on by a particular staff member . Basically need to establish what work.gets done by WFH
                                  • Allow non admins to create folders

                                    I want to allow users in my company to create their own reporting based upon selected Datasources, however they currently can't create folders to save their work in. This can only be done by admins, which seems excessive. Is this possible ?
                                  • Episode I : Exploring the World of Custom Functions in Zoho Desk

                                    Hello Again! Welcome to an Automation Adventure in the World of Zoho Desk Join us on a journey to explore how custom functions can enhance and extend the capabilities of Zoho Desk. Automation is everywhere. From robotic arms assembling products on factory
                                  • Free Webinar - Overview of Zoho Sign and latest updates- May 2025

                                    Hello there! What's the digital alternative to endless paperwork? Zoho Sign. Designed for digital-first businesses, Zoho Sign is a complete digital signature solution with powerful features and seamless integrations that streamline your entire workflow.
                                  • Multiple Forecast configurations

                                    Hi all Is it possible to have multiple Forecast Configurations? That is, not just multiple forecasts, but rather different forecasts assigned to different configurations? The use case here is that we currently have the Forecast module configured for Revenue
                                  • Next Page