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
    • Sticky Posts

    • What's New in Zoho Analytics - February 2026

      Hello Users! We're back with another round of updates for Zoho Analytics. This month's release focuses on giving you greater flexibility in how you visualize, manage, and act on your data - with new features like custom visualizations, remote MCP server,
    • What's New in Zoho Analytics - January 2026

      Hello Users! We are starting the year with a strong lineup of updates, marking the beginning of many improvements planned to enhance your analytics experience. Explore the latest improvements built to boost performance, simplify analysis, and help you
    • What's New in Zoho Analytics - November 2025

      We're thrilled to announce a significant update focused on expanding your data connectivity, enhancing visualization capabilities, and delivering a more powerful, intuitive, and performant analytics experience. Here’s a look at what’s new. Explore What's
    • What's New in Zoho Analytics - October 2025

      Hello Users! We're are back with a fresh set of updates and enhancements to make data analysis faster and more insightful. Take a quick look at what’s new and see how these updates can power up your reports and dashboards. Explore What's New! Extreme
    • What’s New in Zoho Analytics – September 2025

      Hello Users!! In this month’s update, we’re raising the bar across multiple touchpoints, from how you bring in data, plan and track projects to how you design and brand your dashboards. We’ve added the all-new Gantt chart for project visualization, expanded

    Nederlandse Hulpbronnen


      • Recent Topics

      • Zoho Bookings - Provide Appointment System ID in Zoho Flow Variable

        Hi Bookings Team, It would be great if you could provide the system record ID for appointments as a variable in Zoho Flow trigger outputs and Fetch Appointments action. This would allow us to create a dymanic URL which can be clicked by a staff user to
      • Cliq iOS can't see shared screen

        Hello, I had this morning a video call with a colleague. She is using Cliq Desktop MacOS and wanted to share her screen with me. I'm on iPad. I noticed, while she shared her screen, I could only see her video, but not the shared screen... Does Cliq iOS is able to display shared screen, or is it somewhere else to be found ? Regards
      • Pro rata holiday allowance for variable part time employees

        We have some part time staff who work variable hours and their holiday allowance is based on pro rata system. In Zoho people is there any way to calculate their holiday allowance based on actual hours worked? I.e. worker is entitled to a pro-rata amount of 5.6 weeks holiday, which is equivalent to 12.07% of hours worked over a year. So part time staff's holiday is accrued at a rate of 12.07% per hour worked. Thanks
      • Introducing SlyteUI : From Idea to a Working Interface in Minutes

        Hello everyone! Are you spending hours building basic UIs? Does even the smallest customization feel like a major task? CRM customization should feel intuitive and straightforward, not time consuming or exhausting. SlyteUI makes this possible by simplifying
      • Trying to validate user's age through DoB

        We have a form where a user enters their birthdate. We'd like a rule that counts whether the user is over or under 25 years of age. This amounts to 9125 days elapsed between today and an easy way to do this would be to use conditional rule Is Before [x]
      • Invoice date is incorrect

        I'm trying to pull a report of Sales Orders and filter by Invoice Date. (ex: all of 2025) Unfortunately, the filter still returns lots of SO's from other years including 2026 which would be impossible to have an invoice in 2025 from a 2026 order. It's
      • Query on Tracking Visitor Activity with Zoho Forms & SalesIQ

        I have a query regarding Zoho Forms, Zoho SalesIQ, and Zoho CRM. I have embedded a Zoho Forms form on my website, and I’m trying to understand if it’s possible to capture a user’s website activity (such as pages visited, visit count, etc.) when they submit
      • Understanding tasks

        So we have a module linked to contacts called Equipment and that in turn has a related module called service calls. Often on a service call we want add a task but we are not able. I think it needs a link to the contact. WE therefore tried to add tasks
      • How to transfer files from Creator file upload fields to CRM file upload fields

        This article describes how to transfer files from Zoho Creator file upload fields to Zoho CRM file upload fields. I'm posting it here because the current documentation does not fully and accurately describe how to do this with certain file types (PDF,
      • Add Comprehensive Accessibility Features to Zoho Writer

        Hello Zoho Writer Team, We hope you are doing well. We would like to submit a feature request to enhance Zoho Writer with a full set of accessibility tools, similar to the accessibility options already available in the Zoho Desk agent interface. 🚧 Current
      • ¿Puedo migrar mi sitio desde WordPress a Zoho? ¿Zoho admite herramientas con código personalizado?

        ¡Hola comunidad! Estoy evaluando la posibilidad de migrar mi sitio web https://calculadoradenotas.cl/ desde WordPress a una solución Zoho, y tengo algunas dudas técnicas que espero puedan aclararme. Mi sitio no es solo informativo: es una herramienta
      • Make Task ID and Issue ID columns filterable like all other columns

        Hello Zoho Projects Team, We hope you are doing well. We would like to submit a feature request regarding the Task ID and Issue ID columns in Zoho Projects list views. Current Limitation: As of now, in both the Tasks and Issues modules, almost every visible
      • Improve Back Navigation (X Button) Inside Nested Milestones, Task Lists, Tasks, and Issues

        Hi Zoho Projects Team, We would like to request an improvement to the X / close button navigation behavior inside milestones. Currently, the milestone area behaves as a nested hierarchy (tree structure): Project → Milestone → Task List → Task So when
      • Add a way to connect Log360 Cloud logs with Zoho analytics

        Hi, Several month ago Log360 Cloud was added to zoho one - and this is great. But as far as I see there is no prebuilt way to connect Zoho analytics to the logs we have in Log360 Cloud. Please add a prebuilt connection like we have for so many other zoho
      • Extend GitHub Commit Integration to Tasks in Zoho Projects

        Hello Zoho Projects Team, We hope you are doing well. We would like to submit a feature request regarding the current GitHub integration in Zoho Projects, specifically the ability to link commit information. Current Limitation: As of now, Zoho Projects
      • Enable Screen Recording in Zoho WorkDrive Mobile Apps (Android & iOS)

        Hi Zoho WorkDrive Team, How are you? We are enthusiastic Zoho One users and rely heavily on Zoho WorkDrive for internal collaboration and content sharing. The screen-recording feature in the WorkDrive web app (similar to Loom) is extremely useful- however,
      • Zoho Project - Total Forecasted Hours

        Does anyone know an easy way to get a total of all the forecasted hours across the portfolio? I'm aware trying to create a custom report that can generate this figure for each task and the total up but the completion percentage doesn't seem to come back
      • Bank Fees - Allow User to Choose Expense Account

        Bank fees from Customer Payments can only be debited against the system Bank Fees account due to hard-coding. The user should be able to select which expense account those fees are debited against. The use case is pretty straightforward. A business like
      • Attendance Kiosk Mode for all Employees

        Is it possible to assign a tablet (ios/android) so that all employees can check in or out using a simple QR code? If not would it be possible to integrate attendance with a web app that also has a tablet app that works in kiosk mode? If yes can anyone recommend such an app?
      • How can we get payment status updates for payment links in Zoho Books using webhooks?

        When we create and share a payment link in Zoho Books, is there a way to know when the payment is updated? Can we use webhooks to get real-time updates for payment link status instead of checking manually?
      • Update to CRM Custom Buttons: Collect Users' Location

        Hello everyone! Buttons in Zoho CRM allow you to extend the default CRM capabilities for your bespoke business needs. It provides the flexibility to connect to any third-party application to perform necessary actions. Wouldn't it be better, if those buttons
      • Introducing note actions and dynamic visibility in Kiosk Studio

        Hello all, We are introducing enhancements to Kiosk Studio that will improve the product scope and meet your custom needs more precisely. What's new? Add notes as Actions: You can add notes to CRM records as kiosk Actions, as well as insert merge fields
      • CRM x WorkDrive: We're rolling out the WorkDrive-powered file storage experience for existing users

        Release plan: Gradual rollout to customers without file storage add-ons, in this order: 1. Standalone CRM 2. CRM Plus and Zoho One DCs: All | Editions: All Available now for: - Standalone CRM accounts in Free and Standard editions without file storage
      • Challenges with data consistency when integrating Zoho with other systems

        In setups where Zoho (CRM/Inventory) is connected with external systems like eCommerce platforms, maintaining consistent and reliable data across systems can be challenging. Some common issues that tend to come up: Product data mismatches (variants, pricing
      • Logging Calls not working

        For some reason, logging calls is not working anymore. Description of problem: On a Lead's record, in the "Open Activities" block, clicked on "Add New" Clicked on "Call", clicked on "Log a call" Nothing happens. If I click on "Schedule a call" nothings
      • Announcing Zoho Sheet desktop app for macOS and Windows (Beta)

        Hello Sheet users, We know you’ve been waiting for this one. It has always been the top priority on our roadmap to provide a single native desktop app for macOS and Windows that works both online and offline. Today, we are excited to announce that the
      • Zoho Mail Android app update: UI revamp

        Hello everyone! We are excited to share that the first phase of the Zoho Mail Android UI revamp is now live. In this update, we have redesigned navigation bar at the bottom to quickly access the Email, Calendar, Contacts, and Settings modules. Also, the
      • Records Per Page

        Can we get max number of records per page increased from 100 to 250 and 500 please? Or alternatively allow us to use the Zoho Sheet View for all records and not just the max 100 showing on the page? thanks.
      • This campain is on hold

        Trying to start a Zoho Campaign for our company but the error message when we try to send it out it this: This campaign is on hold Our compliance team needs additional information about your sender domain address. They'll reach out to you shortly via
      • Blueprint transitions on locked records

        We use the ability to automatically lock records (quotes, sales orders, etc.) based on criteria, such as stage. For instance, if a quote has been sent to a client, the quote is then locked for further edits. Our ideal quote stage process is: Draft>Sent>Won.
      • Whatsapp Limitation Questions

        Good day, I would like to find out about the functionality or possibility of all the below points within the Zoho/WhatsApp integration. Will WhatsApp buttons ever be possible in the future? Will WhatsApp Re-directs to different users be possible based
      • Sales Ordes removes TAX at random for some or all prodcuts, even after creating it

        Hi at Zoho CRM, in the Sales Orders section, when Creating, Cloning or editing any Sales Orders the TAX of one or all products gets "unchecked" at random. All of our products ALWAYS must have the same TAX selected automatically. At least it used to be
      • Turning off self view in Zoho Meeting

        Hi Is it possible to turn off/hide self view in meetings?  Cant see any options for it. Thanks
      • CRM to Desk Contact Sync Issues?

        Hello, I noticed none of the contact changes in CRM are being transferred over to Desk. Anyone else experiencing this? I checked the sync history in desk and it confirms it - it doesn't show "X Contacts Synced" like it normally does.
      • Please add Deluge code editing for Forms

        Assuming Forms is built on the same codebase as Creator, etc., it would be wonderful to be able to edit form code, cut and paste form elements across different forms, share code with other users, and so on.
      • Is there any workaround or method to restrict user login to a particular device

        Hi, I want to restrict my CRM user from accessing the Zoho CRM/Zoho One application to the company provided devices only. As far as I have explored there is no direct way to do this. Is there any workarounds that will allow me to do this. I want to ensure
      • Email from CRM being Blocked or Marked as Spam by Google (and maybe more)

        In the past 24 hours we’ve noticed that emails sent via Zoho CRM are being blocked or flagged as phishing, particularly by Google. The issue seems to occur specifically when emails contain links. URLs like www.domain.com or www.example.com are automatically
      • All new Address Field in Zoho CRM: maintain structured and accurate address inputs

        Availability Update: 29 September 2025: It's currently available for all new sign-ups and for existing Zoho CRM orgs which are in the Professional edition exclusively for IN DC users. 2 March 2026: Available to users in all DCs except US and EU DC. 24
      • Zoho Tables - Form default entry

        How so I set a form field to be populated with an entry from another table? I created a view on my source table limiting the results to just the one thing I want to see. When adding a Client via my Add Client form, I want the Client Type field to already
      • Payment Card or Identity form-fill from Vault?

        Hello! I'm working on replacing Bitwarden with Vault and one issue I've run into is that I can't find any option to fill address and payment forms from Payment Card or Identity info that has been saved in Vault. Is there a way to do this? Is it a planned
      • Next Page