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

    • Invitation-Based User Access in Zoho Analytics

      Hello everyone, We’re rolling out an important update on how users are added to your Zoho Analytics Organization and Workspaces. Previously, when admins added users, they were automatically added to the organization. Moving forward, to improve security
    • [Webinar] Evolving BI & Analytics in the Age of AI

      Artificial intelligence is redefining how data is collected, analyzed, and leveraged across industries. As businesses strive to become more agile and insight-driven, traditional BI and analytics must transform to meet new demands. AI-first organizations
    • 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
    • Recent Topics

    • PASSWORD

      Hello, I'm Joyce, my client used zoho for password sharing, he did share the canva but once I clicked on it it will not automatically log-in, instead I need to log-in again. My question is my boss log-in first to his gmail and use his gmail to log-in
    • Products in time entry

      Morning, Is there a way to add the product field to the time entry layout? Giving us the ability to identify a product per time entry. Thanks Rudy
    • Kaizen #195: Frequently Asked Questions on Bulk Read API and Bulk Write API

      × 🎉 Nearing 200th Kaizen Post – We Want to Hear from You! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights help us improve! Got Ideas? Tell Us! ✨ We are thrilled to be nearing the 200th post
    • admin problem

      i can't to reach for the panel that i will create another mail to our company account i have admin access but i can't reach the panel our Company name Scale point my mail asmaa@dcalepointhub.com please can you help me Thanks
    • 554 5.7.1 : Recipient address rejected: user [username] does not exist

      Hi, I mistakenly altered my shopify email settings (where my domain is managed), but immediately reverted them, however now I have a strange email issue. I can send emails just fine, but cannot receive them. I have tried all troubleshooting steps but
    • Problema para enviar y recibir correos

      Buenos días, mi cuenta de correo secretaria@construccmauro.com presenta problemas y no me permite ni me envía ni recibe correos, me sale este error.No fue posible enviar el mensaje; Motivo: 554 5.1.8 Correo electrónico bloqueado saliente.  Aprende más., Agradezco
    • Agent working hours

      Hi, I know it is possible to set company business hours but is it possible so that agents can have different ones? I.e. some agents cover later hours on specific weeks - can these be set so those agents that are "working" get notified about tickets etc. 
    • Legit email address?

      Hello, I received emails from zohoadmin@biznetvigator.com with a password expiry notice. is that a legitimate email?
    • Problem with the "Search" function

      Hi, I've been using Workdrive for a few days and lately, the files I added in Workdrive don't appear in the search. It looks like my latest files aren't indexed. Are you aware of this issue ? 
    • SMTP Authentication Fails with App Password – “535 Authentication Failed” Error

      Hello, I'm trying to send transactional emails from my WordPress website using the WP Mail SMTP plugin with Zoho Mail (smtp.zoho.com on port 465 with SSL). I've created and used a Zoho Mail app-specific password for SMTP, and verified that: The email
    • Deluge script issue : Mismatch of data type expression. Expected BIGINT but found STRING

      I'm building a Zoho Creator form to take inputs for Email, Bootcamp Name, and Tag. If a record with the same Email + Bootcamp exists in a custom module (bootcampattendence), I want to update it by adding a new tag. If it doesn’t exist, I want to create
    • Survey end date extension

      Hi, Is there any way to extend the end date of my survey? I needed more time in finding respondents that is why I need to extend the end date of my survey. Help. Thanks
    • Problem with signature in a forwarded mail

      Dear All, In my email account I created a signature and I unchecked the 'Place signature above the quoted text for relies and forwards' My question is, when I am trying to forward an email, sometimes I need to insert my signature so I select it from the
    • Out of Office not working

      I have set up out of office on zoho mail, however, it does not reply to every mail sent to it. I have tested it by sending several test messages from multiple different email accounts, I will get a response to some of them, but not all of them this is
    • Error message that says only images and no text

      I filled out a template for a weekly newsletter with text and images throughout but when I click save and next an error message comes up that says "Campaign content has only images and no text" which is not true at all. I have no idea how to fix this issue and don't know where the problem is. 
    • Link project invoices to sales orders

      As a business owner and project manager I create estimates with my clients which then become sales orders. When billing for project work I want to invoice against the agreed sales order. I see that I can create invoices and link them to sales orders in
    • Contacts Don't Always Populate

      I've noticed that some contacts can easily be added to an email when I type their name. Other times, a contact doesn't appear even though I KNOW it is in my contact list. It is possible the ones I loaded from a spreadsheet are not an issue and the ones
    • Cannot add zoho email to gmail acc

      I'm trying to set up my zoho mail to connect to my gmail acc but no matter what I try I always get this problem. What should I do now? Password is up-to-date. Authentication failed. Please check your username/password. [Server response: 535 Authentication
    • 553 Relaying disallowed - Invalid Domain

      I have this error "553 Relaying disallowed. Invalid Domain" when sending an email to any email address. But I still receiving email from other emails. I checked MX, DKIM, SPF and it's ok. Could you check and help? Thanks
    • Function #25: Automatically generate purchase orders from a sales order

      We kicked off the "Function Fridays" series with the goal of helping you automate your everyday accounting tasks. As we delve into today's post, I'm delighted to announce that we're here to present the 25th custom function in this series. While it is
    • Turn off Organization Contact List

      We need to be able to turn off the Organization Contact list for some of our staff. Once the Organization Contact list is turned off for a user, we would then like to be able to create a list of contacts on per user basis that would not be editable by
    • Unable to send massage Reason 553/ Relaying Disallowed. Invalid Domain

      Cant sed massages from my email. All massages from my domain received the same issue Unable to send massage Reason 553/ Relaying Disallowed. Invalid Domain
    • Kaizen #194 : Trigger Client Script via Custom buttons

      Hello everyone! Welcome back to another interesting and useful Kaizen post. We know that Client Scripts can be triggered with Canvas buttons and we discussed this with a use case in Kaizen#180. Today, let us discuss how to trigger Client Script when a
    • Make record entry more organized with the Sections component in Wizards

      Hello everyone! Wizards in Zoho CRM have always helped you break long record-detail pages or large sets of fields into multiple screens, making data entry smoother and more user-friendly. Now, we’re taking a major step forward with a new Sections component—giving
    • GSTIN Public Search API

      Does zohobooks have an api using which i can search GST numbers and get their details?
    • External @zoho.com mail stopped working today, yesterday no issues

      Hello, Over a sudden mail stopped being delivered to inbox. Domain is standard zoho.com worked no problem yesterday, today nothing in inbox. Sent several test emails from corporate account & from gmail.com, logs show acceptance by zoho mail server, but
    • Suggestion: Smart Purchasing Module for Zoho Inventory and Zoho Books

      Suggestion: Smart Purchasing Module for Zoho Inventory and Zoho Books As an active user of Zoho Inventory and Zoho Books, I’ve noticed that the suite already provides valuable tools for managing inventory, analyzing trends, and processing purchase orders.
    • Adding bank details to the contact through API

      How to add bank-related information to the contact while creating it using API? The account number needs to be encrypted before sending it through API but not sure how to encrypt and get those values. Please guide me in this.
    • average cost display

      Hello there, I'm using the average cost valuation method, and the cost price shown in the Items menu should reflect the latest WAC (Weighted Average Cost). However, it doesn't update automatically. Is there a way to display the current average cost without
    • Apple Mail issues

      Dear Sir or Madam, I am trying to configure my Zoho mail account to Apple Mail. It is not working. It seems to need more information (perhaps a path prefix?) than is shown in your tutorial (<www.zoho.com/mail/help/apple-mac-imap.html>). After plugging
    • Assistance Required for Migrating Data from Odoo Inventory to Zoho Inventory

      Hi Team, I previously used Odoo Inventory to manage my stock and transactions, and I’ve now moved to Zoho Inventory. I’d like to migrate my data from Odoo—including product details, stock, purchase, and vendor information- into Zoho Inventory for continuity
    • I need my entire Zoho Account (mail/organization) deleted!

      Hey! I used zoho for my email aaron@aaronglass.me with domain forwarding/mx all that. I got rid of the mx info on my godaddy account and still Google won't let me sign up with my aaron@aaronglass.me email. (I'm moving to Google for business) SO! I was
    • Error: "Invalid Element warehouse_id" when sending order to Zoho

      i'm getting the error "Invalid Element warehouse_id" when trying to send an order to Zoho via API. Has anyone faced this issue before? How can I fix it?
    • No way to sell individual and case units smoothly in Zoho

      Currently, Zoho Inventory handles inventory primarily at the case level, which can be challenging for businesses that frequently need to break down cases into individual units for retail sales or smaller shipments. Although Zoho Inventory currently supports
    • How to handle multiple languages (Resume parser, Job opening, job board, career site) ?

      We are looking to bring Zoho Recruit to a market that needs to support 2 languages. The first thing we need to make sure is that the Candidate be flag with the language they speak. This is easy enough make a custom field that as the desire languages.
    • Multi-Card Selection and Cross-Zobot Copy-Paste Functionality

      Dear Zoho SalesIQ Team, We’d like to suggest a productivity-enhancing feature for the Zobot builder in Zoho SalesIQ: the ability to select multiple cards (modules) at once using a selection area, and then copy-paste them either within the same canvas
    • Shipping Labels

      Hi, Can we generate Packing Labels for manual shipping same as the format as Airway Bill in zoho books or inventory?
    • Add a Way to Duplicate Cards in the Same Canvas (Retain All Settings)

      Dear Zoho SalesIQ Team, We would like to request a new feature in Zoho SalesIQ: the ability to duplicate an existing card within the same canvas while retaining all of its information and settings, including conditions, configurations, and display preferences.
    • Managing two books in Zoho Books

      is it possible to effectively manage two separate books within Zoho Books? My organization is considering handling accounting for two distinct subsidiaries, and we would like to understand the best way to achieve this within the Zoho Books.
    • Unit Measurement Sign,

      Dear Zoho Support Team, I hope you are doing well. We recently purchased Zoho Inventory based on recommendations of your team that “it would be a great fit for our Shopify operations”. However, we are currently facing several urgent issues that require
    • Next Page