Add insights comparing 2 line metrics

Question & Answer
, , , ,
1

I come from Data Engineering community and new to BIE world. Trying to learn building dashbaord in QuickSight. Need some help to get started on few things I’m trying to build. So that I can learn and start doing it myself post the initial learnings. I have a line chart which contains data

week_end_day         category       # of tables
June 8                 S3                   100
June 8                 Redshift             100
June 15                S3                   150
June 15                Redshift              50

I want to add following insight

For the latest reporting week June 15, there are 25% of the tables still under Redshift.
The adoption of S3 has increased by X% from 100 to 150.

How can I achieve this? I tried using Insight - Metric comparison but unable to figure this out.

2

Hello @Gangadhar_Reddy

Welcome to the community! Thanks for your question!

To create the desired insights in Amazon QuickSight, follow these steps:

Step 1: Data Preparation

Ensure your data is correctly formatted and uploaded to Amazon QuickSight.

Step 2: Creating Calculations

You need to create calculated fields to determine the percentages and differences you want to display in your insights.

1. Percentage of Tables in Redshift

Create a calculated field to determine the percentage of tables in Redshift for the latest reporting week.

  • Go to your dataset.
  • Click on “Add” → “Calculated field”.
  • Name it Redshift_Percentage.
  • Use the following formula:

Example

ifelse(
  {category} = 'Redshift' and {week_end_day} = 'June 15', 
  {# of tables} / sumOver({# of tables}, [{week_end_day}]) * 100, 
  NULL
)

2. Percentage Increase in S3 Adoption

Create another calculated field to calculate the percentage increase in S3 adoption.

  • Add a new calculated field.
  • Name it S3_Adoption_Increase.
  • Use the following formula:

Example

ifelse(
  {category} = 'S3' and {week_end_day} = 'June 15', 
  ({# of tables} - sumIf({# of tables}, {week_end_day} = 'June 8' and {category} = 'S3')) / sumIf({# of tables}, {week_end_day} = 'June 8' and {category} = 'S3') * 100, 
  NULL
)

Step 3: Adding Insights

Use the calculated fields in your insights.

1. Insight for Percentage of Tables in Redshift

  • Add an insight visual to your analysis.
  • Drag the Redshift_Percentage field to the insight.
  • Customize the narrative to display: “For the latest reporting week June 15, there are ${Redshift_Percentage}% of the tables still under Redshift.”

2. Insight for S3 Adoption Increase

  • Add another insight visual to your analysis.
  • Drag the S3_Adoption_Increase field to the insight.
  • Customize the narrative to display: “The adoption of S3 has increased by ${S3_Adoption_Increase}% from 100 to 150.”

Final Customization

  • Format the insights to ensure they are clear and concise.
  • Adjust the narratives and formatting as needed to match your requirements.
  • Control the week_end_day values through the parameter. In the above examples, it is hardcoded.

By following these steps, you should be able to achieve the desired insights in your QuickSight dashboard.

1 Like
3

While adding an insight we have to chose one of the computation type. Are you suggesting to calculate everything outside and use “Total Aggregation” computation to do it?

5 
ifelse(
  {category} = 'Redshift' and {week_end_day} = 'June 15', 
  {# of tables} / sumOver({# of tables}, [{week_end_day}]) * 100, 
  NULL
)

The above calculation is giving error -

Mismatched aggregation. Custom aggregations can't contain both aggregated and nonaggregated fields, in any combination.
6

Hi @Gangadhar_Reddy

Here are the examples of parameters, calculated fields, and custom narrative for your requirement. Tune this based on your requirement.

Make sure the week_end_day field is a date field.

Custom Narrative:

For the latest reporting week formatDate(${LatestWeek}, 'dd-MMM-yyyy'), there are RedshiftMaximum.metricValue.value % of the tables still under Redshift. The adoption of S3 has increased by ceil((((S3LatestWeek.metricValue.value - S3PreviousWeek.metr… % from S3PreviousWeek.metricValue.value to S3LatestWeek.metricValue.value from the previous week formatDate(${PreviousWeek}, 'dd-MMM-yyyy').

Parameter:

  1. LatestWeek - Date Field
  2. PreviousWeek - Date Field

Calculated Fields :

1. Redshift_Tables_Latest_Week
sum(ifelse(category = 'Redshift' and {week_end_day} = ${LatestWeek}, {# of tables}, 0))

2. Total_Tables_Per_Week
sumOver(sum({# of tables}), [{week_end_day}])

3. Redshift_Percentage
ifelse(sum(ifelse(category = 'Redshift' and {week_end_day} = ${LatestWeek}, 1, 0)) > 0, {Redshift_Tables_Latest_Week} / {Total_Tables_Per_Week} * 100, 0)

4. S3_Tables_Latest_Week
sum(ifelse(category = 'S3' and {week_end_day} = ${LatestWeek}, {# of tables}, 0))

5. S3_Tables_Previous_Week
sum(ifelse(category = 'S3' and {week_end_day} = ${PreviousWeek}, {# of tables}, 0))

image
image1067×117 9.34 KB