Post Syndicated from Lillie Atkins original https://aws.amazon.com/blogs/big-data/announcing-the-new-amazon-quicksight-community/

On February 22, 2022, we launched our new Amazon QuickSight Community. Here you can ask and answer questions, network with and learn from other Business Intelligence (BI) users from across the globe, access learning content, and stay up to date with what’s new on Amazon QuickSight—all in one place!

In this post, we discuss some of the features of the QuickSight Community and show you how to sign up, start posting, create a profile and set up notifications.

Content on the Community

The QuickSight Community has three main sections:

1. Question and Answer – A discussion forum where you can ask your QuickSight questions and get answers from community experts. You can also showcase your QuickSight expertise by sharing your knowledge with others.
   
2. Learning Center – A hub for on-demand QuickSight content, including how-to videos workshop videos, articles, and additional educational resources.
   
3. Announcements – Stay up-to-date on the latest QuickSight launches, blogs, feature demo videos, and monthly newsletters. We continuously update this section as new content related to QuickSight is added.
   

Join the QuickSight Community

This QuickSight Community does not require any login to search or browse existing content. You only need to create an account if you want to interact with the community (such as liking posts, replying to posts, and posting your own questions).

This is a public community, so be careful not to post any confidential or private information.

Sign up for the Community

To sign up, complete the following steps:

1. On the QuickSight Community home page, choose Sign Up.
   
2. You can either use your existing Amazon.com account, or create a new login.
   
3. Read the Community guidelines.

You can now contribute to the Community. Welcome!

Post a question

You need to be logged in with an account to start asking questions. Before you post a question, search the recommended questions to make sure your question hasn’t already been answered.

To post a question, complete the following steps:

1. On the Question and answer page, choose New Question.
   
2. Enter the information to create your post, including a title and tag.
3. Choose New Question.
   

Set up your profile

To view your profile and change the settings, complete the following steps:

1. Choose your user icon at the top of the page and choose the person icon.
   
2. Select the section you are interested in:
   
3. If you chose Preferences, you can control security and notification preferences, and update your profile details.
   

Set up notifications

You can set up notification preferences to be alerted on a specific post, channel, or tag.

To set up notifications on a post, choose the post and then choose the notification icon to set your notification preferences.

To get notified on a specific channel, go to the channel (in this case the Question and answer channel) and choose the notification bell to set your notification preferences.

To get notified on a specific tag, complete the following steps:

1. Choose the additional options icon.
   
2. Choose Tags.
   
3. Choose the tag you are interested in.
   
4. Choose the notification icon and set your notification preferences.
   

Conclusion

In this post, we discussed the new Amazon QuickSight Community, and how you can sign up for it, create a post, edit your profile, and set up notifications. The QuickSight Community is a one-stop shop for all of your QuickSight learning needs, and a place to network with other BI users from around the globe. Start exploring today!

About the Authors

Lillie Atkins is a Product Manager for Amazon QuickSight, Amazon Web Service’s cloud-native, fully managed BI service.

Mia Heard is a Product Marketing Manager for Amazon QuickSight, AWS’ cloud-native, fully managed BI service.

Post Syndicated from Deepak Murthy original https://aws.amazon.com/blogs/big-data/enable-users-to-ask-questions-about-data-using-natural-language-within-your-applications-by-embedding-amazon-quicksight-q/

Amazon QuickSight Q is a new machine learning-based capability in Amazon QuickSight that enables users to ask business questions in natural language and receive answers with relevant visualizations instantly to gain insights from data. QuickSight Q doesn’t depend on prebuilt dashboards or reports to answer questions, which removes the need for business intelligence (BI) teams to create or update dashboards every time a new business question arises. Users can ask questions and receive visual answers in seconds directly from within QuickSight or from web applications and portals. In this post, we look at how you can embed Q in your web applications or portals.

Solution overview

You can now embed Q in your application without any custom development. Q is a fully managed cloud-native BI offering that you can easily embed without requiring expertise within your team to develop and maintain this capability. You can easily democratize your data and scale your insights to a growing user base, while ensuring you only pay for usage with Q’s unique pay-per-question pricing model.

Applications can authenticate users with any identity provider of choice (such as Active Directory, Amazon Cognito, or any SAML-based federated SSO provider that your organization uses) and act on behalf of the user to get access to the Q question bar. This means that every user receives a secure, personalized question answering experience while requiring no user-facing QuickSight-specific authentication. This enables a novel experience to provide insights within your application with minimal upfront work and allows you to focus on your core application functionality! QuickSight Q embedding is available in QuickSight Enterprise Edition and Q-supported Regions.

To facilitate an easy embedding experience, AWS has also launched the Amazon QuickSight Embedding SDK (JavaScript) and a rich set of Q-specific functionalities. The QuickSight Embedding SDK lets you efficiently integrate Q in your application pages, set a default topic, enable topic selection, set themes, and control Q search bar behavior. This helps you roll out Q to your users faster.

To embed Q in your application, you must complete the following high-level steps:

1. Set up permissions to generate embedded Q URLs.
2. Generate a URL with the authentication code attached.
3. Embed the Q search bar URL.

Set up permissions to generate embedded Q URLs

In this step, you set up permissions for your backend application or web server to embed the Q search bar. This task requires administrative access to AWS Identity and Access Management (IAM). Each user who accesses the Q search bar assumes a role that gives them QuickSight permissions to retrieve a Q-embedded URL.

To make this possible, create an IAM role in your AWS account. Associate an IAM policy with the role to provide permissions to any user who assumes it. The IAM role needs to provide permissions to retrieve embedding URLs for a specific user pool. With the help of the wildcard character *, you can grant the permissions to generate a URL for all users in a specific namespace. Or you can grant permissions to generate a URL for a subset of users in specific namespaces. For this, you add quicksight:GenerateEmbedUrlForRegisteredUser.

The following sample policy provides these permissions:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "quicksight:GenerateEmbedUrlForRegisteredUser"
            ],
            "Resource": "arn:partition:quicksight:region:accountId:user/namespace/userName"
        }
    ]
}

Also, if you’re creating first-time users who will be QuickSight readers, make sure to add the quicksight:RegisterUser permission in the policy.

The following sample policy provides permission to retrieve an embedding URL for first-time users who will be QuickSight readers:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": "quicksight:RegisterUser",
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Effect": "Allow",
            "Action": [
              "quicksight:GenerateEmbedUrlForRegisteredUser"
            ],
            "Resource": [
              "arn:partition:quicksight:region:accountId:user/namespace/userName"
            ]
        }
    ]
}

Finally, your application’s IAM identity must have a trust policy associated with it to allow access to the role that you just created. This means that when a user accesses your application, your application assumes the role on the user’s behalf and provisions the user in QuickSight.

The following example uses a role called embedding_quicksight_q_search_bar_role, which has the sample policy preceding as its resource:

{
    "Version": "2012-10-17",
    "Statement": {
        "Effect": "Allow",
        "Action": "sts:AssumeRole",
        "Resource": "arn:aws:iam::11112222333:role/embedding_quicksight_q_search_bar_role"
    }
}

Generate a URL with the authentication code attached

In this step, you authenticate your user and get the embeddable Q topic URL on your application server. If you plan to embed the Q bar for IAM or QuickSight identity types, share the Q topic with the users. When a user accesses your app, the app assumes the IAM role of the user. If that user is new, the app adds the user to QuickSight, then passes an identifier as the unique role session ID.

These steps make sure that each viewer of the Q topic is uniquely provisioned in QuickSight. It also enforces per-user settings, such as the row-level security and dynamic defaults for parameters.

The following example code performs the IAM authentication on the user’s behalf. This code runs on your app server:

import com.amazonaws.auth.AWSCredentials;
import com.amazonaws.auth.BasicAWSCredentials;
import com.amazonaws.auth.AWSCredentialsProvider;
import com.amazonaws.regions.Regions;
import com.amazonaws.services.quicksight.AmazonQuickSight;
import com.amazonaws.services.quicksight.AmazonQuickSightClientBuilder;
import com.amazonaws.services.quicksight.model.GenerateEmbedUrlForRegisteredUserRequest;
import com.amazonaws.services.quicksight.model.GenerateEmbedUrlForRegisteredUserResult;
import com.amazonaws.services.quicksight.model.RegisteredUserEmbeddingExperienceConfiguration;
import com.amazonaws.services.quicksight.model.RegisteredUserQSearchBarEmbeddingConfiguration;

/**
 * Class to call QuickSight AWS SDK to get url for embedding the Q search bar.
 */
public class RegisteredUserQSearchBarEmbeddingConfiguration {

    private final AmazonQuickSight quickSightClient;

    public RegisteredUserQSearchBarEmbeddingConfiguration() {
        this.quickSightClient = AmazonQuickSightClientBuilder
                .standard()
                .withRegion(Regions.US_EAST_1.getName())
                .withCredentials(new AWSCredentialsProvider() {
                                     @Override
                                     public AWSCredentials getCredentials() {
                                         // provide actual IAM access key and secret key here
                                         return new BasicAWSCredentials("access-key", "secret-key");
                                     }

                                     @Override
                                     public void refresh() {}
                                 }
                )
                .build();
    }

    public String getQuicksightEmbedUrl(
            final String accountId, // AWS Account ID
            final String topicId, // Topic ID to embed
            final String userArn // Registered user arn to use for embedding. Refer to Get Embed Url section in developer portal to find how to get user arn for a QuickSight user.
    ) throws Exception {
        final RegisteredUserEmbeddingExperienceConfiguration experienceConfiguration = new RegisteredUserEmbeddingExperienceConfiguration()
                .withQSearchBar(new RegisteredUserQSearchBarEmbeddingConfiguration().withInitialTopicId(topicId));
        final GenerateEmbedUrlForRegisteredUserRequest generateEmbedUrlForRegisteredUserRequest = new GenerateEmbedUrlForRegisteredUserRequest();
        generateEmbedUrlForRegisteredUserRequest.setAwsAccountId(accountId);
        generateEmbedUrlForRegisteredUserRequest.setUserArn(userArn);
        generateEmbedUrlForRegisteredUserRequest.setExperienceConfiguration(QSearchBar);

        final GenerateEmbedUrlForRegisteredUserResult generateEmbedUrlForRegisteredUserResult = quickSightClient.generateEmbedUrlForRegisteredUser(generateEmbedUrlForRegisteredUserRequest);

        return generateEmbedUrlForRegisteredUserResult.getEmbedUrl();
    }
}

To generate the URL that you can embed in your app, call the GenerateEmbedUrlForRegisteredUser API operation. This URL is valid for 5 minutes, and the resulting session is valid for up to 10 hours. You can configure the session validity by setting the sessionLifetimeinMinutes parameter for GenerateEmbedURL APIs. The API operation provides the URL with an auth_code value that enables a single-sign on session. The following code shows an example response from generate-embed-url-for-registered-user:

//The URL returned is over 900 characters. For this example, we've shortened the string for
//readability and added ellipsis to indicate that it's incomplete.
{
 "Status": "200",
 "EmbedUrl": "https: //dashboards.example.com/embed/620bef10822743fab329fb3751187d2d...",
 "RequestId": "7bee030e-f191-45c4-97fe-d9faf0e03713"
} 

Embed the Q search bar URL

In this step, you embed the Q search bar URL in your website or application page. You can do this with the QuickSight Embedding SDK, which allows you to do the following:

• Place the Q search bar on an HTML page
• Pass parameters into the Q search bar
• Handle error states with messages that are customized to your application

Embed the Q search bar in your webpage by using the QuickSight Embedding SDK or by adding this URL into an iFrame. If you set a fixed height and width number (in pixels), QuickSight uses those and doesn’t change your visual as your window resizes. If you set a relative percent height and width, QuickSight provides a responsive layout that is modified as your window size changes. When you use the QuickSight Embedding SDK, the Q search bar on your page is dynamically resized based on the state. By using the QuickSight Embedding SDK, you can also control parameters within the Q search bar and receive callbacks in terms of page load completion and errors.

The following example code shows how to use the generated URL. This code is generated on your app server:

<!DOCTYPE html>
         <html>
     
         <head>
             <title>QuickSight Q Search Bar Embedding</title>
             <script src="https://unpkg.com/[email protected]/dist/quicksight-embedding-js-sdk.min.js"></script>
             <script type="text/javascript">
                 var session
     
                 function onError(payload) {
                     console.log("Do something when the session fails loading");
                 }
     
                 function onOpen() {
                     console.log("Do something when the Q search bar opens");
                 }
     
                 function onClose() {
                     console.log("Do something when the Q search bar closes");
                 }
     
                 function embedQSearchBar() {
                     var containerDiv = document.getElementById("embeddingContainer");
                     var options = {
                         url: "https://us-east-1.quicksight.aws.amazon.com/sn/dashboards/dashboardId?isauthcode=true&identityprovider=quicksight&code=authcode", // replace this dummy url with the one generated via embedding API
                         container: containerDiv,
                         width: "1000px",
                         locale: "en-US",
                         qSearchBarOptions: {
                             expandCallback: onOpen,
                             collapseCallback: onClose,
                             iconDisabled: false,
                             topicNameDisabled: false, 
                             themeId: 'theme12345',
                             allowTopicSelection: true
                         }
                     };
                     session = QuickSightEmbedding.embedQSearchBar(options);
                     session.on("error", onError);
                 }
     
                 function onCountryChange(obj) {
                     session.setParameters({country: obj.value});
                 }
             </script>
         </head>
     
         <body onload="embedQSearchBar()">
             <div id="embeddingContainer"></div>
         </body>
     
         </html>

For this example to work, make sure to use the QuickSight Embedding SDK to load the embedded dashboard on your website using JavaScript. You can get the SDK in the following ways:

• Download the QuickSight Embedding SDK from GitHub. This repository is maintained by a group of QuickSight developers.
• Download the latest QuickSight Embedding SDK version from npmjs.com.
• If you use npm for JavaScript dependencies, download and install it by running the following command:

  npm install amazon-quicksight-embedding-sdk

The following screenshot is an example of the embedded Q question bar and a QuickSight dashboard to support natural language questions and analysis of pharmaceutical clinical trial data. You can try out this topic and other such topics in an embedded application demo.

Summary

Enterprises can empower users to ask questions about data in plain English within their applications by embedding the QuickSight Q question bar. Embedding Q into your application is straightforward and requires no custom development from your team.

Get started with a free trial of QuickSight Q.

About the Authors

Deepak Murthy is a Senior Product Manager for Amazon QuickSight, AWS’s cloud-native, fully managed BI service. Deepak started his career with Staples, developing enterprise data warehouse solutions. Later, he was the architect of data warehouse and analytics solutions at Wells Fargo, AMC, and Blackhawk Network. Deepak is excited about the potential of self-service analytics and improving data accessibility by enabling new natural language interactions with data, and looks forward to helping customers leverage these latest analytics innovations.

Rob Foley is a Software Development Engineer for Amazon QuickSight, AWS’s cloud-native, fully managed BI service. Rob began his career with AWS, and has been a member of the QuickSight team for over 1.5 years. He has development experience in a breadth of services and stacks, primarily having worked on data-centric applications like Q.

Post Syndicated from Sumitha AP original https://aws.amazon.com/blogs/big-data/use-analyticsiq-with-amazon-quicksight-to-gain-insights-for-your-business/

Decisions are made every day in your organization that impact your business. Making the right decision at the right moment can deeply impact your organization’s growth and your customers. Likewise, having the right data and tools that generate insights into the data can empower your organization’s leaders to make the right decisions.

In the healthcare industry where decisions directly impact an individual’s wellness, having the right data to generate the right insight into the individual experience through the lens of social determinants of health can greatly improve health outcomes and save lives. Understanding the unique social situations of the individuals they serve, from access to transportation, technology to economic, food security and more, allows healthcare providers to address disparities and give all their patients an equal opportunity to achieve their desired level of health.

For example, let’s say a healthcare organization or government agency wants to better understand the factors that affect public health in order to improve the quality of life for various ethnic groups, based on data.

In this post, we show you how to use AnalyticsIQ datasets and Amazon QuickSight to generate valuable insights that could improve your organization’s decision-making. we use the AnalyticsIQ Social Determinants of Health Sample Data dataset to gain insights into the relationship between ethnicity and health, as well as how the social determinants impact the health and wellness of individuals.

Solution overview

The following architecture diagram outlines the components of this solution:

The solution consists of the following components:

• The AnalyticsIQ Social Determinants of Health Sample Data dataset to demonstrate an example of the impact of social determinants on health and wellness. This dataset is listed as AnalyticsIQ in the Extended Provider Program.
• AWS Data Exchange to subscribe to the sample AnalyticsIQ dataset from AWS Marketplace.
• An Amazon Simple Storage Service bucket to store the AnalyticsIQ sample dataset.
• QuickSight enterprise edition to generate visualizations using the sample dataset.

To implement the solution, you complete the following high-level steps:

1. Export the dataset to an S3 bucket.
2. Sign up for a QuickSight subscription.
3. Create a QuickSight dataset.
4. Create visualizations in QuickSight.

Prerequisites

To run this solution, you must have an AWS account. If you don’t already have one, you can create one.

Export the dataset to an S3 bucket

To start working with your dataset, you must subscribe to the dataset and then export the data to an S3 bucket. Complete the following steps:

1. If you don’t already have a bucket, navigate to the Amazon S3 console, and choose Create bucket.
2. Give a unique name for your bucket.

Make sure that you create the bucket in the us-east-1 Region.

3. To subscribe to the sample dataset, follow this link. On the AWS Data Exchange console, choose Continue to subscribe.
4. On the Complete subscription page, choose Subscribe.
5. For Select Amazon S3 bucket folder destination, choose your S3 bucket.

The subscription process can take up to 2 minutes to complete.

6. On the AWS Data Exchange Console, under My subscriptions in the navigation pane, choose Entitled Data.
7. Under Products, expand Social Determinants of Health Sample Data – Offline, and choose the AnalyticsIQ sample dataset.
8. On the Revisions tab, select the revision and choose Export to Amazon S3.
   
9. Enter the name of the S3 bucket you created for this dataset.
10. Leave the other options as default.
11. Choose Export.

You can view the dataset in your S3 bucket under the prefix Sample-Data.

Sign up for a QuickSight subscription

To sign up for a QuickSight subscription, complete the following steps:

1. On the AWS Management Console, open QuickSight.
2. Choose Sign up for QuickSight and choose Enterprise.
3. For QuickSight account name, enter a unique name.
4. Enter a valid email.
5. Under Allow access and autodiscovery for these resources, select Amazon S3 and choose Select S3 buckets.
   
6. Choose the S3 bucket that you created earlier, and choose Finish.
7. After your QuickSight account is created, choose Go to QuickSight account.

Create a QuickSight dataset

To create your dataset, complete the following steps:

1. Using a local text editor, create a JSON file. Copy the following content and replace the placeholder with the name of the bucket that you created earlier:

   {
         "fileLocations": [
             {
                 "URIPrefixes": [
                          "https://<Your BucketName>.s3.amazonaws.com/Sample-Data/"
                 ]
             }
          ],
        "globalUploadSettings": {
                 "format": "TSV"
           }
   }

2. On the QuickSight console, choose New data set on the Datasets page.
3. Choose S3.
4. For DataSource, enter a name.
5. Choose Upload and upload the JSON file.
6. Choose Connect.
7. Choose Visualize.

The following screenshot shows your imported sample data:

Create visualizations in QuickSight

Let’s visualize the average number of cars by various ethnic groups. For more information about the fields, refer to the Key Data Points section on the AWS Marketplace listing.

1. Choose the sheet and choose the vertical bar chart under Visual types.
   
2. From the Fields list, drag EthnicIQ_v2 to X axis and Number_of_Autos to Value.
3. Choose Aggregate as Average.
   

Now you can create a visualization for urgent care visits by ethnic groups.

4. Choose +Add, and choose Add Visual.
5. Choose a pivot table under Visual types.
6. From the Fields list, drag EthinicIQ_v2 to Rows and HW_Urgent_Care_Visits_SC to Values.
7. Choose Aggregate as Average.
8. Choose the HW_Urgent_Care_Visits_SC field in the pivot table, and choose Sort descending.
   

Similarly, you can add more visualizations as shown in the following images.

From these visualizations created from sample data, you can see that a person’s use of healthcare services reduces when they have less access to transportation. The AA ethnic group has fewer cars compared to the other groups. The wellness score for the AA group is low when compared to the others. Transportation barriers could be a major factor here. Job satisfaction also contributes to wellness levels. Furthermore, the sample data indicates that the Hispanic community has the highest likelihood of recent urgent care visits. Does this mean these groups aren’t getting enough preventative care, leading to more urgent care visits?

Sleep and job satisfaction play a critical role in affecting stress levels, as well as overall health. This would be a critical factor for people who work shifts. What measures can be taken to increase the sleep quality for that set of people?

These are just few of the innumerable valuable analyses that you can create from the AnalyticsIQ Social Determinants of Health Sample Data dataset. These insights are valuable for various groups of people, such as health professionals, preventative care, employee care, scientists, and governments, to empower communities and help build better public health and social determinant solutions.

Clean up

To avoid incurring ongoing charges, complete the following steps to clean up your resources:

1. On the QuickSight console, on the Analyses page, choose the details icon on the analysis you created, and choose Delete.
2. On the QuickSight start page, on the Datasets page, choose the dataset that you created earlier, then choose Delete Data Set.
3. On the Amazon S3 console, on the Buckets page, select the option next to the name of your bucket, and then choose Delete at the top of the page.
4. Confirm that you want to delete the bucket by entering the bucket name into the text field, then choose Delete bucket.

Conclusion

In this post, we showed you how you can use the AnalyticsIQ Social Determinants of Health Sample Data dataset to gain insights into society’s health and wellness. We also showed you how you can generate easy-to-understand visualizations using QuickSight. Amazon QuickSight allows dashboards to be shared with 1000s of users without any servers, and with pay-per-session pricing. QuickSight dashboards can also be easily embedded in SaaS applications or corporate portals for sharing insights with all users. You can explore the AnalyticsIQ dataset more on the AWS Data Exchange console. For queries related to the AnalyticsIQ dataset, you can reach out directly to the support team at [email protected].To learn more about the features of QuickSight, refer to Amazon QuickSight Features.

About the Author

Sumitha AP is an AWS Solutions Architect based in Washington DC. She works with SMB customers to help them design secure, scalable, reliable and cost effective solutions in the AWS cloud.

Post Syndicated from Emily Zhu original https://aws.amazon.com/blogs/big-data/add-comparative-and-cumulative-date-time-calculations-in-amazon-quicksight/

Amazon QuickSight recently added native support for comparative (e.g., year-over-year) and cumulative (e.g., year-to-date) period functions which allow you to easily introduce these calculations in business reporting, trend analysis and time series analysis. This allows authors in QuickSight to implement advanced calculations without having to use complicated date offsets in calculations to achieve such datetime-aware comparisons.

In this post, we introduce the new period functions and their capabilities, and demonstrate several typical use cases. We also discuss several scenarios to extend the usage of the period functions, which will be useful in more advanced situations.

New period functions

Before we demonstrate use cases, let’s go over the new period function suite and see what new functions we now support. We can divide period functions into two main groups: comparative (period over period) functions and cumulative (period to date) functions.

Comparative (period over period) functions

You can use period over period functions to compare measures at different time periods, such as year, quarter, and month. For example, you can compute a year-over-year increase in sales, or week-over-week percentage revenue changes.

A typical comparative period function has the syntax periodOverPeriodDifference(measure, date, period, offset), with two optional arguments: period and offset.

You can use the period argument in the function to define the period granularity of the calculation. The granularity of YEAR means year-over-year computation, Quarter means quarter-over-quarter, and so on. If the period argument is left empty, the calculation changes based on the period granularity that is chosen (in the field well) to be displayed in the visual.

You can also use the offset argument to specify how many periods apart you want to compute the comparison. For instance, a period of a quarter with an offset of 2 means comparing against the previous two quarters.

Note that period and offset have to be both specified or both left empty. You can’t specify just one of them.

The following table summarizes the three available period over period functions.

Cumulative (period to date) functions

You can use period to date functions to calculate metrics within a given period-to-date window. There are two main types of cumulative functions.

• “OverTime” functions, which are table calculations and return outputs for each row in the visual.

E.g., you can use periodToDateCountOverTime with WEEK granularity to compute a series of week-to-date new customer counts to track the fluctuation of customer engagement.

• Aggregation functions, which output an aggregated value for a fixed period-to-date time range.

E.g., periodtoDateSum with YEAR granularity returns a single value for the total of the metric from the beginning of the year to the endDate provided in the formula. If left blank, the default endDate takes now(), which is the moment when users load the dashboard.

The following table summarizes the period to date functions.

Use case 1: Using a period over period function to analyze sales growth in fixed and dynamic granularity

Let’s dive into how period over period functions can support typical business and financial use cases. The following example uses periodOverPeriodDifference to calculate YoY sales growth. Because we have Segment and Region in the visual, the YoY sales is calculated for each segment and region.

We define the measure of YoYSales with the following formula: YoYSales=periodOverPeriodDifference(sum(Sales),{Order Date},YEAR,1)

The first argument, sum(Sales), tells the function to calculate based on this measure. The second argument, Order Date, specifies the date/time column from which Year information is extracted. The third argument, YEAR, fixes the granularity of this calculation. When this optional argument is specified, this measure always returns YoY (not QoQ or MoM) no matter how Order Date is selected (in the field well) to be displayed in the visual. The fourth argument, 1, specifies the offset of the comparison. In this example, it means we want to compare the sales of each order date with the same date of the previous year. The measure returns empty for order dates of 2018, because no previous periods exist to be compared with.

The period functions are working with totals and subtotals. By adding the total for columns into the visual, you can see the total sales and total YoYSales for each region.

If you leave the optional argument of period granularity empty, meaning change the formula to PoPSales=periodOverPeriodDifference(sum(Sales),{Order Date})as shown in the following example, the time period of the calculation is then determined by the granularity of Order Date displayed on the visual. In the following example, Order Date is chosen to display at quarter level (in the field well), so PoPSales dynamically calculates the QoQ sales growth. Changing Order Date to the monthly level updates the measure to calculate MoM. For PoPSales, only Q1 2018 returns empty because that’s the only quarter that doesn’t have a previous quarter to compare with.

If we add YoYSales from the previous example to this visual, it calculates YoY sales growth at the quarter level (compares sales of Q1 2019 with Q1 2018). This demonstrates the difference between a fixed granularity and a dynamic granularity of period over period functions.

The period over period functions can differentiate between a positive change (increase) and negative change (decrease). Therefore, when we add the conditional formatting to the visual, it’s very straightforward to see the financial performance of each period (green is good, red is bad).

Similarly, you can use periodOverPeriodPercentDifference to calculate relative sales growth over time. You can add dimensions into the visual to dive further into business insights, such as analyzing the breakdown of each business segment’s sales change by quarter, and their contribution to the total sales increase. We use the formula PoPSales%=periodOverPeriodPercentDifference(sum(Sales),{Order Date}).

Use case 2: Using a period to date function to track YTD sales in table calculations and aggregations

Similar to period over period functions, the period to date function suite provides a quick and easy way to calculate year-to-date (YTD) or quarter-to-date (QTD) metrics. In the following example, we use the formula of YTDSumoverSales=periodToDateSumOverTime(sum(Sales),{Order Date},YEAR), and YTDtotalcountSales=periodToDateSumOverTime(count(Sales),{Order Date},YEAR) to calculate YTD sales and YTD total number of transactions.

Contrary to period over period functions, the third argument of period to date functions, period, isn’t optional. Therefore, the calculation granularity is always fixed. In this example, with the granularity defined as YEAR, this measure always calculates YTD, instead of QTD or MTD. Because Order Date is displayed at the monthly level, this calculation outputs the YTD sales of each month, and starts over again in January for the next year. As shown in the result table, YTDSumoverSales of January 2018 is the monthly sales of January 2018, and YTDSumoverSales of February 2018 is the monthly sales of January 2018 plus that of February 2018. And YTDSumoverSales of January 2019 goes back to the monthly sales of January 2019.

You can further dive into the details by populating the calculations in a line chart, and adding more dimensions into the analysis. The following example shows the YTD weekly sales growth trend for each region along the past four years, and uncovers some interesting sales competition between AMER and EMEA in year 2021.

In addition to the table calculations, the aggregation period functions are particularly useful when you need to build KPI charts to evaluate YTD metrics in a real-time manner. In the following example, we use the aggregation period to date functions to build two KPI charts to track the YTD total sales, and YTD total number of transactions. For the date December 26, 2021, the timestamp results match the corresponding table calculations for the date of December 26, 2021 in the table. The following table summarizes the formulas.

Advanced use case 1: Date/time awareness with period functions

Period functions are not only easier to define and read, they’re also date/time-aware, meaning the functions are calculated based on a date/time-based offset instead of a fixed number of rows. It can solve two major problems that were not possible to be addressed before.

Period functions can handle varying period duration

If you want to calculate the daily MoM sales increase, you can’t use a fixed offset on each month because the number of days of each month are different (31 days for January and 28 or 29 days for February).

Period functions are calculated based on calendar dates instead of a fixed offset. In the following example, we use the formula MoMsalesDiff=periodOverPeriodDifference(sum(Sales),{Order Date},MONTH,1). The daily MoM increase is calculated correctly based on the day of the month. The sales of the first day of the month are compared with the first day of the previous month, and the same applies to all other days. (Visuals are duplicated for demonstration purposes.)

Period functions can handle sparse (missing) data points

Not all datasets can guarantee a complete set of dates. In the previous example, sales data of January 1, 2018, is missing. Using the workaround based on a fixed offset can cause a problem here because we compare February 1, 2018, with a different date instead of January 1, 2018. Period functions always compare measures by date/time offsets so that only desired dates are compared. In the previous example, MoMsalesDiff shows empty for February 1, 2018, because of the missing data of January 1, 2018.

Advanced use case 2: Nesting period functions with other calculations

Now that we can use period over period and period to date functions to create calculated fields, we can nest these functions with other calculations to drive more advanced analysis.

For example, you may want to know for each year, what are the top 10 weeks of the year in regards to week-over-week sales growth. You can do this by calculating WoWSales first: WoWSales=periodOverPeriodDifference(sum(Sales), {Order Date}, WEEK, 1). Then you nest it with the denseRank window function: RankWoWSales=denseRank([WoWSales DESC],[{YEAR}]). This wouldn’t be possible using the fixed-row based workaround, which is implemented using on-visual calculations instead of calculated fields. In the following visual, the top 10 weeks of each year with the highest sales growth are fetched by a simple filter on RankWoWSales.

You can even nest the period functions with other period functions to generate interesting insights. For example, you can calculate a monthly YoY growth based on the monthly YTD number of transactions. The following formula demonstrates the capability of nesting a YTD calculated field inside a YoY calculated field:

YTDtotalsountSales=periodToDateSumOverTime(count(Sales),{Order Date},YEAR)
YoYYTDSalesCount=periodOverPeriodDifference(YTDtotalcountSales,{Order Date},YEAR,1)

The results in the following visual show a YoY growth based on a YTD accumulated number of transactions instead of the absolute monthly numbers.

Advanced use case 3: Partial period comparisons

Finally, we discuss a third advanced use case: partial period comparison. Imagine it’s November 15, 2021 (which is the 46th day of the last quarter of 2021), and you want to calculate 4 to compare the performance of this quarter with past quarters, but only using the first 46 days of each quarter instead of the whole quarter. This requires a calculated field using periodOverPeriodDifference nested with the sumIf() window function.

The following example demonstrates using a nested calculated field to address this use case:

QuarterToDate=periodToDateSumOverTime(sum(Sales), {Order Date}, QUARTER)
PartialQTDSales=sumIf(Sales, dateDiff(truncDate("Q", {Order Date}), {Order Date}, "HH") <= dateDiff(truncDate("Q", now()), now(), "HH"))
PartialQoQQTDSales=periodOverPeriodDifference(sumif(Sales, {Order Date} <= addDateTime(dateDiff(truncDate("Q", now()), now(), "HH"), "HH", truncDate("Q", {Order Date}))), {Order Date}, QUARTER, 1)

PartialQTDSales computes how many hours from the beginning of this quarter to the current date and uses sumIf() to calculate the total sales of that period of each quarter. partialQoQQTDSales then nests the periodOverPeriodDifference function with PartialQTDSales to find the partial QoQ differences. Such a comparison based on a partial period isn’t feasible without the new date/time-aware period functions.

Conclusion

In this blog, we introduced new QuickSight period functions which enable quick and powerful date/time-based calculations. We reviewed comparative and cumulative period functions (i.e., period over period and period to date), discussed two major use cases (fixed vs. dynamic granularity and table calculation vs. aggregation), and extended the usage to three advanced use cases. Period functions are now generally available in all supported QuickSight Regions.

Looking forward to your feedback and stories on how you apply these calculations for your business needs.

About the Authors

Emily Zhu is a Senior Product Manager at Amazon QuickSight, AWS’s cloud-native, fully managed SaaS BI service. She leads the development of QuickSight core analytics and calculations capability. Before joining AWS, she was working in the Amazon Prime Air drone delivery program and the Boeing company as senior strategist for several years. Emily is passionate about potentials of cloud-based BI solutions and looks forward to helping customers to advance in their data-driven strategy making.

Rajkumar Haridoss is a Senior Software Development Engineer for AWS QuickSight. He is the lead engineer on the Query Generation team and works on back-end calculations, query planning, and query generation layer in QuickSight. Outside of work, he likes spending quality time with family and 4-year-old.