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

    Access your files securely from anywhere



                          Zoho Developer Community




                                                  • Desk Community Learning Series


                                                  • Digest


                                                  • Functions


                                                  • Meetups


                                                  • Kbase


                                                  • Resources


                                                  • Glossary


                                                  • Desk Marketplace


                                                  • MVP Corner


                                                  • Word of the Day


                                                  • Ask the Experts



                                                            • Sticky Posts

                                                            • 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
                                                            • [Webinar] Why AI Must Stand on the Solid Foundation of Analytics & Business Intelligence

                                                              Are you prepared for the age of AI? Organizations that have a solid foundation in analytics and business intelligence will be in a better position to harness GenAI, agentic apps, and intelligent automation. Join Doug Henschen and Mike Ni, VPs and Principal
                                                            • [UI Update] Mandating New UI for all Zoho Analytics Users

                                                              Dear Users, We’re pleased to share that the new and improved Zoho Analytics 6.0 user interface has already been embraced by many users, delivering a faster, cleaner, and more efficient experience. For the Zoho Analytics users who are still using the old
                                                            • 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
                                                            • What's New in Zoho Analytics - March 2025

                                                              Hello Users, We're back with the latest set of enhancements and improvements aimed at improving your analytics experience. Common Query Table Expression (CTE) support in Query Tables We’ve added support for Common Table Expressions (CTE) in Query Tables,


                                                            Manage your brands on social media



                                                                  Zoho TeamInbox Resources



                                                                      Zoho CRM Plus Resources

                                                                        Zoho Books Resources


                                                                          Zoho Subscriptions Resources

                                                                            Zoho Projects Resources


                                                                              Zoho Sprints Resources


                                                                                Qntrl Resources


                                                                                  Zoho Creator Resources



                                                                                      Zoho CRM Resources

                                                                                      • CRM Community Learning Series

                                                                                        CRM Community Learning Series


                                                                                      • Kaizen

                                                                                        Kaizen

                                                                                      • Functions

                                                                                        Functions

                                                                                      • Meetups

                                                                                        Meetups

                                                                                      • Kbase

                                                                                        Kbase

                                                                                      • Resources

                                                                                        Resources

                                                                                      • Digest

                                                                                        Digest

                                                                                      • CRM Marketplace

                                                                                        CRM Marketplace

                                                                                      • MVP Corner

                                                                                        MVP Corner





                                                                                          Design. Discuss. Deliver.

                                                                                          Create visually engaging stories with Zoho Show.

                                                                                          Get Started Now


                                                                                            Zoho Show Resources


                                                                                              Zoho Writer Writer

                                                                                              Get Started. Write Away!

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

                                                                                                Zoho CRM コンテンツ










                                                                                                  Nederlandse Hulpbronnen


                                                                                                      ご検討中の方




                                                                                                            • Recent Topics

                                                                                                            • Cannnot find criteria tab

                                                                                                              We need to be able to segment contacts based on an account view or field. The online help shows a screen with three options: Under Sync Details there is All Contacts; Custom view; Criteria.  The latter is missing from my screen.  I need it. I also want to know if I delete contacts from campaigns, will they still remain in CRM? 
                                                                                                            • Dial Charts in Analytics using field values for colored ranges

                                                                                                              Hey all! It would be supremely helpful if we could use field values for ranges in Analytics for dial charts. We currently display an analytics report to our sales team showing how close they are to reaching the next tier for commission payouts. We update
                                                                                                            • Give Permission for User test in Development/Staging modes

                                                                                                              Hi Everyone, I am using Zoho Creator C6. Where there are development and staging modes. Is it possible to give special / temporary permission for users to test directly in development and staging? Or do you have any other ideas? Thank you very much.
                                                                                                            • A couple of minor enhancements to Workflows

                                                                                                              Last updated on September 17, 2024: These enhancements were initially available for early access, and we've now enabled them for all users. We are elated to announce a couple of enhancements to custom functions in our Workflows! Say hello to: "Source"
                                                                                                            • South Africa Tax Edition is now Available!

                                                                                                              Managing taxes just got easier for field service businesses in South Africa! With Zoho FSM's new South Africa tax edition, field service businesses operating in South Africa can now effortlessly stay compliant with local tax regulations. Easily manage
                                                                                                            • How to overcome Zoho Deluge's time limit?

                                                                                                              I have built a function according to the following scheme: pages = {1,2,3,4,5,6,7,8,9,10}; for each page in pages { entriesPerPage = zoho.crm.getRecords("Accounts",page,200); for each entry in entriesPerPage { … } } Unfortunately, we have too many entries
                                                                                                            • Introducing ICR in Zoho CRM: Transform your printed text into digital data

                                                                                                              From writing on papyrus in the ancient times to creating a humble record in your CRM, the world may have evolved with how it used to record data, but data entry as such has not been simplified. It is still a repetitive and arduous chore on which businesses
                                                                                                            • How to Add Custom Sections in the Product Page?

                                                                                                              Hi Zoho team, I’m currently using the Tranquil theme on Zoho Commerce and I have a question regarding the product page layout. Right now, under each product, I can see expandable sections like Product Details and Specifications. I’d like to add two additional
                                                                                                            • Bulk user onboarding for Cliq Channels in a jiffy

                                                                                                              As developers, we frequently switch between coding, debugging, and optimizing tasks. The last thing we want is to be burdened by manual user management. Adding users one by one to a channel is tedious and prone to errors, taking up more time than we could
                                                                                                            • Weekly Tips: Secure your attachment downloads with Zoho Mail

                                                                                                              Safety is one of our main concerns, whether it’s about device security or online protection. We use tools like fingerprint scanners, facial recognition, and two-factor authentication to keep our devices and email accounts secure. We use methods like OTP
                                                                                                            • Issues with Dashboard Filter and KPI in Zoho Analytics (CAGR)

                                                                                                              Hi everyone, I'm trying to build a CAGR (Compound Annual Growth Rate) KPI in Zoho Analytics, but I'm running into some issues with filter synchronization. Here's the scenario: I created two test reports: One that filters results from 2021 to 2025. Another
                                                                                                            • How Would I get data from a network drive to be feed into creator program.

                                                                                                              What's the easiest and cleanest solution for this?
                                                                                                            • Adding Product Photos through Import or In Bulk

                                                                                                              I am about to add about 1000 products in the CRM. I am shocked that there is no way to add product photos through the Import feature. I'll be spending days adding product photos.... Very dissapointing. Anyone know of a work around to add product photos
                                                                                                            • Announcing Multi-language Support in Zoho FSM

                                                                                                              Zoho FSM now speaks your language. The much-awaited multi-language support is now available in Zoho FSM. The following languages are supported in Zoho FSM: Dutch (Nederlands) English - United Kingdom English - United States French (français) French -
                                                                                                            • How to sync Zoho CRM Quotes with Zoho Books/Finance Estimates or Quotes

                                                                                                              Hi everyone, We’re building quotes in the Zoho CRM Quotes module because of its strong CPQ features and better communication options (multiple contacts, email customization, etc.). However, these don’t sync directly with Zoho Books/Finance for invoicing.
                                                                                                            • Quiz of the Day on Dashboard

                                                                                                              Hi team, Is it possible to enter a feature for enabling a widget as a quiz for the day. This widget can have one question popping up daily from the list of some pre-fed questions and the employees need to either type a response or can select from a multiple-choice
                                                                                                            • Email content strategy

                                                                                                              This is basically a couple of questions about the best practices when it comes to the use of colors as part of the message sent through the email campaign. 1 - Is there a guide or recommendation as to what are the best colors for text/background associated
                                                                                                            • Canvas Button Vanished

                                                                                                              I have a button that has mysteriously vanished. It shows in the canvas builder but then is no where to be found in the actual record and before you ask, yes I have tried to access through incognito mode and same issue.
                                                                                                            • Multiple Tabs in the Dashboard

                                                                                                              Hi team, Can we enable an option to add multiple tabs in a dashboard? This will help to group the content to be displayed to employees and make it more usable and user-friendly intranet space.
                                                                                                            • In the Pivot table in the value section i want to the actual value instead of sum or count

                                                                                                              I am creating Early/late Check and check out in pivot report based on Zoho analytics I need the data to include: Employee Name Late Entry, Early Entry, Early Exit, Late Exit, First In, Last Out, and Total Hours.
                                                                                                            • Introducing Assemblies and Kits in Zoho Inventory

                                                                                                              Hello customers, We’re excited to share a major revamp to Zoho Inventory that brings both clarity and flexibility to your inventory management experience! Presenting Assemblies and Kits We’re thrilled to introduce Assemblies and Kits, which replaces the
                                                                                                            • IMAP as a source option in Dashboards

                                                                                                              Hi Team Can I request IMAP be added as a source option for running a report or creating a KPI in dashboards. I have selected all options and my IMAP linked emails are not showing, I can only check these by going to emails in a contact. Thankyou Josh
                                                                                                            • In Zoho people Check in report the date want come as Colum how i can achive that in zoho people or analytics

                                                                                                              In Zoho people Check in report the date want come as Colum. How i can achive that in zoho people or analytics
                                                                                                            • Canvas responsive preview in different browser window causes re-authentication/verification

                                                                                                              I'm trying to design a view in Canvas. I am testing in a tab with a desktop view, and another tab (on my same desktop/IP) with a responsive view of an iphone. Every time I refresh, it boots me out of the previously used tab. If I forget to refresh the
                                                                                                            • Import subform entries conveniently in CRM

                                                                                                              Dear All,   Subforms have always been crucial for associating additional data with CRM records. You can easily associate line items with parent records and keep track of various details related to your records. We're pleased to announce that we've introduced
                                                                                                            • Canvas: how can I edit theme?

                                                                                                              This says "Theme Colours", and I would like to edit theme colors (and styles ala CSS). How can I edit these "Theme Colours"? I'm a software developer, so just point me in a direction where I can alter code if needed.
                                                                                                            • Cómo convencer a tu manager para venir a Zoholics 2025

                                                                                                              ¿No estás seguro de si a tu manager le convence que asistas a Zoholics 2025? ¡No te preocupes! Aquí te damos algunos argumentos clave que te ayudarán a convencer a tu manager de que participar en este evento es una inversión tanto para ti como para la
                                                                                                            • HTML Email in Zoho Books

                                                                                                              Is it possible to create custom html email template in zoho books. 
                                                                                                            • Using IMAP configuration for shared email inboxes

                                                                                                              Our customer service team utilizes shared email boxes to allow multiple people to view and handle incoming customer requests. For example, the customer sends an email to info@xxxx.com and multiple people can view it and handle the request. How can I configure
                                                                                                            • Can you please let us know how we can use Zoho for multi store?

                                                                                                              Hello Team, Can you please let us know how we can use Zoho for multi store because when we connect our plugin to Zoho and we create a product and then on another store when we create product with same name then product already exist error occurs, so how
                                                                                                            • Contracts Management

                                                                                                              Hello, We are implementing Zoho FSM for our field service operations and, one of the features we are lacking is Service Contract Management. I was told that such feature might be in the pipeline but an estimated launch date for this is not available at
                                                                                                            • Exciting Updates to the Kiosk Studio Feature in Zoho CRM!

                                                                                                              Hello Everyone, We are here again with a series of new enhancements to Kiosk Studio, designed to elevate your experience and bring even greater efficiency to your business processes. These updates build upon our ongoing commitment to making Kiosk a powerful
                                                                                                            • Cancelled appointments still block out timeslot

                                                                                                              I haven't had to test this before, but I've just found a problem. I had an appointment in a certain timeslot, and that appointment was cancelled. However, Bookings won't let another appointment be booked in that slot - it makes that time unavailable until
                                                                                                            • Can you change the width of a template, and/or the size of images we upload to the template?

                                                                                                              my images look good in preview, but are then skewed when sending email. Also, are image sizes "fixed" by which template is being used? Thanks in advance. CV
                                                                                                            • Tip of the Week #53 – Use Zia writing assistant !

                                                                                                              Ever find yourself typing out a quick reply or discussing something with your teammates, and a tiny typo slips through? Or maybe you’re aiming to refine your writing for better clarity and context? That’s where Zia steps in , Zoho's AI powered writing
                                                                                                            • Sheet View for Reports

                                                                                                              Hi - is there any plan to make a "Sheet View" function for reports??? It would be a huge improvement. Moderation Update (30th April 2025): We have a separate post discussing the same requirement. Please follow the below post to stay updated on the progress.
                                                                                                            • When adding or editing a contact in contact tab, the contact or edits cannot be seen in mail tab for up to 24 hours

                                                                                                              When reporting this as a bug, I was informed that it can take up to 24 hours for a newly added or edited contact in the Contacts section to become usable in the Email tab—whether through autocomplete or when selecting recipients via the To button. Frankly,
                                                                                                            • Power of Automation :: SLA for Projects

                                                                                                              Hello Everyone, In this post, we would like to highlight the feature "Time-Based Workflow Rules for Projects" Workflow rules help project managers organize tasks in a project by setting criteria and actions that meet the project requirements. Zoho Projects
                                                                                                            • RouteIQ for Zoho FSM

                                                                                                              Beste, Zou wel top zijn dat we een RouteIQ hebben voor FSM aangezien we constant moeten zien wat de beste route is voor onze monteurs. Nu moeten we een speciale aparte programma hebben om de beste route te berrekenen voor onze monteurs aangezien de planning
                                                                                                            • Let's Talk Recruit: An approval process with zero follow-ups

                                                                                                              Welcome back to our Let's Talk Recruit series! Today, the topic of discussion is the Approval process. We’ll discuss how approval workflows function in recruitment, explore real-world use cases, and show how automation in Zoho Recruit can streamline approvals
                                                                                                            • Next Page