Help! How to design summary number for this case?

Lu_zhang

Here is my map card. in summary number area, I show the top 5 provinces parcel %.

Synchronizing with the map, below is the filters.

when there is none of the filters selected, the summary number on the map is correct.

when there is any of the filters selected, the summary number on the map is incorrect.

below is my design for summary number on beast mode.

Top5省份包裹数 means top5 provinces parcel quantity

快递单号 means parcel / express ID

By SQL data transformation, I added some fields as column for each line of raw data. the fields are 'By Province包裹数‘ , ‘Rank’, 'Top5省份包裹数'.

By Province包裹数 means the total parcel quantity for each province

Top5省份包裹数 means the total parcel quantity for top 5 provinces. when the province of the line of data ranks in total parcel quantity within 5, there is total parcel quantity filled up with the line of data, if not, 0 is filled up with.

So, here is my current design.

my question is, when there is filters selected for the map chart, how can I design to show the right summary number? for examples, to select by store or by time period is the very common business scenarios.

could you please help and give some ideas? thank you very much!

Jonathan53891

Got it. Unfortunately, there is not a native function in Domo that can produce the top N analysis that you are trying to accomplish. However, I believe you are able to achieve this by coding a Python script in the ETL that can dynamically adjust the summary number at the card level after filters have been applied. Please see the Python script below for an example:

# Import the domomagic package into the script
from domomagic import *

# Read data from inputs into a data frame
input1 = read_dataframe('NAME OF ETL TILE PRECEDING THIS PYTHON TILE')

# Function to calculate top N provinces based on parcel quantity
def calculate_top_n_provinces(df, N=5):

  # Group by province and calculate the total parcel quantity for each province

  province_totals = df.groupby('Province包裹数')['快递单号'].count().reset_index()

  province_totals.columns = ['Province包裹数', 'TotalParcels']



  # Sort by total parcel quantity in descending order and get the top N provinces

  top_n_provinces = province_totals.sort_values(by='TotalParcels', ascending=False).head(N)



  return top_n_provinces

# Function to calculate summary numbers for top N provinces
def calculate_summary_numbers(df, top_n_provinces):

  # Filter the input data frame to include only rows from the top N provinces

  filtered_df = df[df['Province包裹数'].isin(top_n_provinces['Province包裹数'])]



  # Calculate the summary number, which is the total parcel quantity for top N provinces

  summary_number = filtered_df['快递单号'].count()


    
  return summary_number

# Get the top 5 provinces based on parcel quantity
top_5_provinces = calculate_top_n_provinces(input1, N=5)

# Calculate the summary number for the top 5 provinces
summary_number = calculate_summary_numbers(input1, top_5_provinces)

# Add the summary number to the data frame for visualization purposes
input1['Top5省份包裹数'] = summary_number

# Write the data frame so it's available to the next action
write_dataframe(input1)

This logic should produce the dynamic calculation logic you are looking for in the summary number of this heat map card, even after when filters are applied.

Jonathan53891

Are you trying to create a dynamic Beast Mode calculation that adjusts based on the filters applied to your dashboard, ensuring the summary number reflects the top 5 provinces' parcel quantities under those conditions? Or are you looking for a static calculation that consistently shows the top 5 provinces' parcel quantities, regardless of any filters applied? There could be a few different solutions depending on whether you need the summary number to update dynamically with filtering or remain constant.

Lu_zhang

https://community-forums.domo.com/main/discussion/comment/96626#Comment_96626

@Jonathan53891 the dynamic one. Can you pls help? thank you!

Jonathan53891

Got it. Unfortunately, there is not a native function in Domo that can produce the top N analysis that you are trying to accomplish. However, I believe you are able to achieve this by coding a Python script in the ETL that can dynamically adjust the summary number at the card level after filters have been applied. Please see the Python script below for an example:

# Import the domomagic package into the script
from domomagic import *

# Read data from inputs into a data frame
input1 = read_dataframe('NAME OF ETL TILE PRECEDING THIS PYTHON TILE')

# Function to calculate top N provinces based on parcel quantity
def calculate_top_n_provinces(df, N=5):

  # Group by province and calculate the total parcel quantity for each province

  province_totals = df.groupby('Province包裹数')['快递单号'].count().reset_index()

  province_totals.columns = ['Province包裹数', 'TotalParcels']



  # Sort by total parcel quantity in descending order and get the top N provinces

  top_n_provinces = province_totals.sort_values(by='TotalParcels', ascending=False).head(N)



  return top_n_provinces

# Function to calculate summary numbers for top N provinces
def calculate_summary_numbers(df, top_n_provinces):

  # Filter the input data frame to include only rows from the top N provinces

  filtered_df = df[df['Province包裹数'].isin(top_n_provinces['Province包裹数'])]



  # Calculate the summary number, which is the total parcel quantity for top N provinces

  summary_number = filtered_df['快递单号'].count()


    
  return summary_number

# Get the top 5 provinces based on parcel quantity
top_5_provinces = calculate_top_n_provinces(input1, N=5)

# Calculate the summary number for the top 5 provinces
summary_number = calculate_summary_numbers(input1, top_5_provinces)

# Add the summary number to the data frame for visualization purposes
input1['Top5省份包裹数'] = summary_number

# Write the data frame so it's available to the next action
write_dataframe(input1)

This logic should produce the dynamic calculation logic you are looking for in the summary number of this heat map card, even after when filters are applied.

Lu_zhang

https://community-forums.domo.com/main/discussion/comment/96746#Comment_96746

so great!!! I need time to digesting all of these above. thank you very much. you are such a great man!

@Jonathan53891

Jonathan53891

My pleasure! Always more than happy to help!