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 - May 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. We are introducing richer data visualizations, smarter AI-powered
    • 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
    • Recent Topics

    • Need Native Support for docx files in Zoho Writer

      Absolutely love Zoho Writer, but often need to share files by email with people who are in the Office ecosystem. Downloading a file as docx, then sending it by email, getting the comments back, converting it to Zoho format, editing it, then converting
    • Mirror Component in Zoho CRM: Access real-time related data without leaving your record

      Hi everyone, This feature is now available for the JP, CA, SA, UAE, and AU DCs. We're excited to bring to you Zoho CRM's mirror component, which presents relevant data on a record's details page and keeps everything users need in one place without having
    • Associate records via the Multi-select lookup RELATED LIST via API

      In the REST API, is there a way to associate records for a multi-select lookup related list other than via the linking module? There are two methods for the lookup: 1. via insert records API 2. via the linking module ...as described in https://help.zoho.com/portal/en/community/topic/kaizen-125-manipulating-multi-select-lookup-fields-mxn-using-zoho-crm-apis
    • What's New - May 2026 | Zoho Backstage

      Another month, another round of improvements in Zoho Backstage. We’ve been busy refining existing experiences and introducing new capabilities to help you plan, manage, and deliver successful events with less effort. Here’s a quick look at everything
    • Product sorting in Zoho Commerce - how can I influence standard behaviour?

      Hello Zoho, I want to sort my products by e.g. name, SKU (Ascending / Descending) or by another attribute. I could neither find any solution in the settings, nor an entry in this help. Can you advice how this would work? Seems the standard sorting is
    • Dynamically prefill ticket fields

      Hello, I am using Zoho Desk to collect tickets of our clients about orders they placed on our website. I would like to be able to prefill two tickets fields dynamically, in this case a readonly field for the order id, and a hidden field for the seller
    • Price Managment

      I have been in discussions with Zoho for some time and not getting what I need. Maybe someone can help explain the logic behind this for me as I fail to understand. When creating an item, you input a sales rate and purchase rate. These rates are just
    • Workflow Assistance in Zoho CRM

      Our client's sales team visits customers on-site and currently fills a physical paper form to capture customer details, and then separately re-enters the same data into Zoho CRM via the mobile app — resulting in double data entry. We want the salesperson
    • Delivery Note without services

      Hi all, It there a possibility to create a delivery note from an invoice without the listed services "idem type: service"? Thank you in advance, Michel
    • Automation Series: Auto-assign Task Followers

      As task progresses, several users are required to stay aware of the updates to plan their upcoming work items efficiently. Manually adding users as followers for an active task might create additional overhead. With this automation, followers can be added
    • Send Email Directly to Channel

      Hi, We are coming from Slack. In Slack each channel has a unique Email address that you can send emails too. I currently forward a specific type of email from my Gmail InBox directly do this channel for Verification Codes so my team doesn't have to ask
    • Terms & Conditions

      I have defined Terms & Conditions in the invoice setting and have set %TermsAndCondition% where I want it to appear but nothing shows up in that area. Is this something we have to define per invoice or can we have a global variable?
    • Billing Status Update

      Hello Latha, I’m working on a new automation (deluge) to fulfill one of our requirements. In this automation, there is a step to update the Work Order billing status from “Not Yet Invoiced” to “Non-Billable.” I tried to find the API information relevant
    • Export to pdf. Images not showing

      My report contains a column with images. When I export it as a list, the images do not show in the PDF document. I have published the report but the images are still not visible. What should I do so that the photos appear in the export?
    • Zoho CRM Kiosk question – Passing Screen Fields to a Function

      I am building a Kiosk in Zoho CRM to create new Supplier (Vendors) records. Current setup: Screen 1 contains user input fields: Supplier Name (Vendor_Name) First Name (First_Name) I created a Deluge function: createSupplier(vendor_name, first_name) The
    • パスワード

      ログインするためのパスワード取得したい。
    • Associate Zoho Project with Deal that is in a specific stage?

      Hi there, When a deal hits a certain stage, I'd like to associate it with an pre-exisiting zoho project? I am using blueprints. Using a custom function and deluge, how could I do this? I was thinking that the easiest option would be the modify the pre-made
    • [Product Update] Locations module migration in Zoho Books integration with Zoho Analytics

      Dear Customers, As Zoho Books are starting to support an advance version of the Branches/Warehouses module called the Locations module, users who choose to migrate to the Locations module in Zoho Books will also be migrated in Zoho Analytics-Zoho Books
    • Canvases Auto-Skewing/Adding Scroll Bars When They Were Not There Prior

      Is anyone else noticing rendering issues in their canvases today? It seems to be mainly icons which now have scroll bars added which makes them all look off, though some fields seemed to revert to squished length as well. Were the icons replaced with
    • New UI for Writer - Disappointed

      I've been enjoying Zoho Writer as a new user for about 6 months, and I really like it. One of my favorite things about it is the menu bar, which you can hide or leave out while still seeing most of your page because it is off to the left. I think this
    • Client Script Button in Related List become invalid

      Hi, I am the admin of our organization. And I setup a client script button in related list to raise payment refund request While this button become non selectable recently. I believe there is something wrong from zoho as this button had run for a year.
    • SMTP outgoing problem

      Hello I have a website where the SMTP email is connected through Zoho Mail SMTP. Today I am no longer receiving emails from the website. Joomla shows that the email was sent successfully, but I do not receive it.
    • Fiscal year setting

      Hi, I am looking into using Zoho Books. I cannot understand the organisation fiscal year setting. Our fiscal year runs from 1 April to 31 March. In the organisation profile, I need to set Fiscal Year to “April to March” and Start Date to “2” for the period
    • Issue with payments on invoices

      Hello, I’m having the following issue. When I create an invoice and try to apply a partial payment in a single transaction, the system does not allow it — it only allows full payment. Is this the expected behavior, or am I missing some configuration?
    • auto add as member the contact owner

      is there a way that i can make a zoho flow that will add the owner of the contact as a member of the chat after the round robin assignment?
    • Welcome to Zoho CommunitySpaces

      Hello everyone, This is your space to ask questions, share ideas, and connect with others building and growing their own communities. For those new here, Zoho CommunitySpaces is a platform for building and managing online communities—from discussion spaces
    • Announcing new features in Trident for Windows (v.1.20.4.0)

      Hello Community, Trident for Windows is here with some new features to elevate your work experience. Let's take a quick look at what's new. Export emails. You can now export emails in the .eml file format as compressed zipped files to create a secure
    • Announcing new features in Trident for Windows (v1.14.5.0)

      Hello Community, Trident for Windows is here with new features to elevate your workplace productivity. Let's take a quick look at what's new. Add and edit contacts Previously, you could view all of your personal and organizational contacts in Trident.
    • Announcing Trident desktop app for Zoho Mail & Zoho Workplace users

      Hello Community, I hope you are doing well and staying safe. As you know, our Mail & workplace teams have been constantly working on adding more value to our offerings to ensure you and your organization continue enjoying your Zoho experience. As part
    • Quick way to add a field in Chat Window

      I want to add Company Field in chat window to lessen the irrelevant users in sending chat and set them in mind that we are dealing with companies. I request that it will be as easy as possible like just ticking it then typing the label then connecting
    • Please Remove the Confirmation Popup

      Currently, every time a recruiter changes the status of a candidate in Zoho Recruit, a popup confirmation appears that requires clicking “OK, Got it” before proceeding. This creates unnecessary friction in the workflow, especially for users handling high
    • Team Module Issues?

      We are testing Team Licenses for use by our Customer Service staff. I created a Teamspace called CSR and only assigned two users to this space: Administrator (me) and “Team License Test.” Team License Test is assigned to the Team User profile, with a
    • Announcing new features in Trident for Windows (v.1.41.5.0)

      Hello Community! Trident for Windows just received an exciting update with new ways to collaborate and stay organized without leaving your workspace. Let’s take a look at what’s new! Integrate Zoho Meeting with Trident. You can now schedule, start, and
    • Zoho ERP | Product updates | June 2026

      Hello users, We launched Zoho ERP on January 23, and since then, our goal has been to help businesses streamline and manage their operations with greater efficiency, flexibility, and control. Since the launch, we've continued to enhance the platform every
    • Zia Agent activation in Zoho Desk forces new Organization creation instead of deploying to existing one

      While attempting to complete the deployment and activation sequence of a new Zia Agent within our existing Zoho Desk environment, the activation process failed on the user interface, throwing a generic error (see print). However, despite the activation
    • Allow native Webhooks to authenticate via Connections

      Allow native Webhooks to authenticate via Connections (Basic Auth) instead of plaintext custom headers Summary Please allow native Webhooks (Workflow Rules > Instant Actions > Webhooks) to authenticate against the destination endpoint using the existing
    • How do page versions work these days?

      I thought that Zoho Wiki had the capability to display previous versions of a page, and optionally reinstate them. But I can't find a current doc on this subject -- is there one? From what I remember, that capability was accessed via the Version number
    • Warehouse -> Locations Transition Causing Errors

      After saying "okay" to the transition from 'warehouses' to 'locations', I've now got shipped Sales Orders that I cannot invoice. How does one proceed?
    • Ability to run report over 180 days

      Is there a reason Zoho limits the ability to run reports for records older than 180 days? In my view, the only reason I can think of is that it forces us to pay for Advanced Analytics (which I do).
    • Cloning a View

      When I clone a View, it doesn't make a copy; it only creates a new copy with the same default fields as if I were creating a new view. What is the purpose of cloning if it doesn't bring in the same fields? Thanks Rudy
    • Next Page