Referencing a Cell for Column Filtering
The Referencing Query Data section explained how to extract and filter records from a specified column of a query result set. All of the examples in that section used hard-coded values as the filtering parameters.
To perform dynamic filtering, use cell references as the filtering parameters. This is particularly useful when the table has multiple levels of row/column headers, and you wish to filter the sub-level based on the parent level.
In this example, you will create a formula table (based on the 'customers' query) with a two-level row header consisting of 'State' and 'Cities within the State'.
1. Create a new report. Add a table with four rows and three columns.
2. Run the 'customers' query in the onLoad script. (See Extracting Data from a Query for more details.) var q = runQuery('cutomers')
3. Select cell[1,0] in the table. Right-click on the cell and select 'Format' from the context menu. This opens the Format panel at the bottom.
4. Click the Data tab in the 'Format' panel.
a. In the 'Binding' panel of the Data tab, select the 'Formula' option. Enter the formula 'q['state']' for cell[1,0].
b. In the 'Expansion' panel of the Data tab, select 'Expand Cell' and choose 'Vertical'.
c. In the 'Cell' panel of the Data tab, set the 'Cell Name' to 'st'. The Data tab of the 'Format' panel should still be open.
5. Select cell[1,1] in the table.
a. In the 'Binding' panel of the Data tab, select the 'Formula' option. Enter the formula q['city'] for cell[1,1].
b. In the 'Expansion' panel of the Data tab, select 'Expand Cell' and choose 'Vertical'.
c. In the 'Cell' panel of the Data tab, set the 'Cell Name' to 'ct'. The table should appear as below.
q['state'] q['city']
6. Preview the report. Notice that all of the cities are listed for each state, not just the cities within the corresponding state. In most cases, it is desirable to see only those cities within the corresponding state. To filter out the cities based on the state, include a field-filtering condition with a reference to the cell 'st'.
7. Change the formula in cell[1,1] to 'q['city@state:$st']'.
8. Preview the report. Notice that the table now lists only those that correspond to the given state. (This example continues in the next section, Referencing Cells in Summary Formulas.)
Referencing Cells in Summary Formulas
Consider the formula table from the previous example in Referencing a Cell for Column Filtering. It consists of a two-level row header listing 'States' and 'Cities within a State'.
In the following example, you will add a formula to count the number of customers within each city.
1. Add the following formula to cell[1,2]:
count(q['customer_id@state:$st;city:$ct'])
This counts all the customers within the given city and state. The table should now appear as shown below.
q['state'] q['city@state:$st'] count(q['customer_id@state:$st;city:$ct'])
2. Preview the report and note the output.
More Articles About Reporting
Analysis Tools for News Publishers - A News & Media Analytics Lead plays a crucial role in leveraging business analysis tools to extract actionable insights from data within the news and media industry. Here's a detailed exploration of how such a professional may use business analysis tools: Audience Engagement Analysis: Tool Usage: Utilizes analytics tools to analyze user engagement metrics, such as click-through rates, time spent on articles, and social media interactions. Objective: Understands audience behavior to optimize content placement, improve user experience, and tailor content to meet audience preferences. Content Performance Monitoring: Tool Usage: Monitors content performance through analytics tools that track page views, shares, and comments. Objective: Identifies high-performing content, enabling data-driven decision-making for content creation, curation, and promotion strategies...
Drawbacks of Amazon QuickSight - While Amazon QuickSight offers numerous benefits as a business intelligence and data visualization tool, it also has its drawbacks. Here are some potential limitations and drawbacks of using Amazon QuickSight: Limited Advanced Analytics Capabilities: QuickSight is primarily focused on data visualization and dashboard creation, which means it may lack some of the advanced analytics features found in other BI tools. For users requiring complex statistical analysis, predictive modeling, or machine learning capabilities, QuickSight may not be the most suitable option. Limited Data Preparation Functionality: QuickSight's data preparation capabilities are not as robust as those offered by dedicated data preparation tools. While it allows for basic data cleaning, filtering, and transformation, users may find it lacking in more advanced data preparation functionalities such as data wrangling, join optimization, and data profiling. Limited Connectivity Options: While QuickSight integrates well with various AWS data sources such as Amazon Redshift, Amazon RDS...
Metrics for Venture Capitalists - Venture capitalists (VCs) play a crucial role in the startup ecosystem by providing funding and strategic support to early-stage companies with high growth potential. To make informed investment decisions and maximize returns, VCs track a variety of key performance indicators (KPIs) and metrics. These indicators help VCs assess the financial health, growth trajectory, and overall performance of portfolio companies. Here are some of the KPIs and metrics commonly tracked by venture capitalists for business intelligence: Revenue Growth Rate: Definition: Measures the percentage increase in a company's revenue over a specific period, typically on a quarterly or annual basis. Performance Insights: A high revenue growth rate indicates strong demand for the company's products or services, effective sales and marketing strategies, and scalability of the business model. Rapid revenue growth is often associated with successful startups poised for expansion and market...
Performance Management in the Music Industry - Business Performance Management (BPM) systems are used in the music industry to streamline operations, improve decision-making, and enhance overall performance. Here's some of the ways that BPM systems are used in the music industry: Revenue and Royalty Tracking: BPM systems help track music sales, streaming revenue, and royalties owed to artists, songwriters, and other rights holders. These systems provide real-time insights into revenue generation, helping music companies manage payments accurately and transparently. Tour and Event Management: BPM systems assist in planning, organizing, and managing music tours and events. They track event schedules, budgets, ticket sales, and expenses, ensuring that tours run smoothly and profitably. Artist Management: BPM systems support artist management by tracking contracts, royalties, tour schedules, and promotional activities. They help in coordinating various aspects of an artist's career, from music releases to marketing campaigns. Digital Distribution: Music distribution platforms use BPM systems to manage the distribution of music to streaming services, online stores, and other digital platforms. These systems facilitate the tracking of sales, streams, and user engagement...
Venture Capital Investment in AI Tech Dashboard - This InetSoft dashboard displays venture capital investment in the AI industry by country. As you can see, investment in AI is booming, with the USA being a dominant player. Interact with the dashboard...