Using Custom Aggregation String Field as a Dimension

Question & Answer
, , ,
1

Hi Everyone,

Reposting as my previous post was locked over the holiday weekend. Thanks for the response @DylanM and I hope you had a good holiday weekend. Your answer made more sense regarding why using that calculated field in a table visual gave that error.

However, my main use for the calculated field ‘test assumption’ would be to use it as a category/dimension in a bar chart to display all stories and epics by their calculated category. But when doing this I still get “Custom aggregation field is not allowed as a dimension” error. Is there a workaround/alternative solution to this? Is it possible to create LAC-W calculated fields on string types or is this just not a supported functionality at the moment? I am doing a Quicksight POC so I want to be as sure as possible before listing this as a current limitation within Quicksight. Thanks again for the help with this, original post for reference below.

Original Post for reference:

I’m trying to create a dashboard that is able to use the ‘epic link field’ in our JIRA data to associate stories with their respective EPIC. The idea being that I can derive a value from the main EPIC to all related stories for analysis.

However, with my current solution I can’t use my calculated fields that accomplish this in a visual. I’ll detail my current approach below and would greatly appreciate if anyone can help me with this.

Attached is a picture of the functionality I want.

JIRA

I am using a calculated field (cEpicLink) that finds the EPIC id value of every record via the formula below:

cEpicLink:
ifelse({Issue Type}=“Kanban Story”,{Custom field (Epic Link)},{Issue key})

Essentially, if the record is a Story then assume the ‘Epic Link’ key value, else if the record is an EPIC then use the main issue key field. Each story is related to one EPIC, so this calculated field allows me to group the epic and all associated stories via the main epic issue key.

Then I am using a second calculated field (testAssumption) to fill in a blank field of all the associated stories with the field value of the Epic. For example, the main EPIC record has an assumption field with the value “Innovation”. I want all of the associated stories to derive this from the EPIC and I’ve done this with the following formula:

testAssumption:
lastValue({Custom field (Assumptions)},[{Custom field (Assumptions)} ASC],[cEpicLink])

Thus, I’ve been able to successfully relate all EPICs and stories by creating the cEpicLink CF which groups all records by the EPIC key and then I’ve copied the value of the main EPIC field to the stories using the testAssumption CF. However, the issue is that when I try to use my testAssumption CF in a visual such as a bar chart I can’t add it as a dimension. This isn’t an issue I’ve ever come across in Tableau when it comes to using calculated fields as dimensions. I’ve tried playing around with converting my calculated fields to a LAC-W but since the field I want is a string that isn’t working either. Any recommendations on this?

2

Hi @emilyzhu – before we dig deep into this – is there a function or some easier solution to accomplish this?

Thanks,

Alex

1 Like
3

Hi @AnthonyN, to address your issue using the calculated field ‘testAssumption’ as a dimension in visualizations like bar charts, there are a few steps and considerations you can take into account:

Approach 1: Using Direct Calculations

  1. Recreate Calculations in the Dataset Preparation:
  • Instead of creating complex calculated fields in the analysis phase, you can preprocess your data in the dataset preparation phase. This can help circumvent limitations with calculated fields in visuals.
  • Use calculated fields directly within the dataset to ensure they are treated as part of the static schema rather than dynamic calculations.

Approach 2: Use of Aggregations

  1. Aggregate Data Appropriately:
  • Aggregated calculated fields can sometimes cause issues. Ensure your calculated field does not aggregate over dimensions that change across your visual.

Approach 3: Re-evaluating Calculated Field Logic

  1. Check the Calculated Field Logic:
  • The current formula for testAssumption might need to be reviewed. Ensure it correctly references the data as intended:
lastValue({Custom field (Assumptions)}, [{Custom field (Assumptions)} ASC], [cEpicLink])
  • Confirm that lastValue is returning the expected results within the dataset.

Approach 4: Use of Dynamic Grouping

  1. Dynamic Grouping and Hierarchies:
  • Consider using dynamic grouping or hierarchies in QuickSight. This allows you to define groupings at the dataset level which can then be used in visuals without recalculating.

Example Workflow:

  1. Dataset Preparation:
  • Create a dataset with your JIRA data, including fields Issue key, Issue Type, Custom field (Epic Link), Custom field (Assumptions).
  • Create calculated fields during this phase:
cEpicLink = ifelse({Issue Type}="Kanban Story", {Custom field (Epic Link)}, {Issue key})
  • Propagate the Assumptions field from EPIC to related stories:
testAssumption = lastValue({Custom field (Assumptions)}, [{Custom field (Assumptions)} ASC], [cEpicLink])
  1. Data Preparation:
  • Use this dataset to create a new analysis.
  • Try using these fields in your visuals, ensuring the dataset preparation step has pre-calculated these values.

Using Calculated Fields in Bar Charts:

  • When creating a bar chart, ensure your testAssumption field is added as a dimension, and your measures (e.g., count of stories, sum of story points) are aggregated appropriately.

Confirming the Limitation:

If after following these steps you still face the issue, it is likely a current limitation within QuickSight. Document this as a limitation and let us know, at AWS, our roadmap is primarily driven by our customers. Your feedback helps us build a better service.

Next Steps:

  1. Consider Transformations Outside QuickSight:
  • Preprocess the data outside of QuickSight if necessary, using tools like AWS Athena/Glue or even a preprocessing script to ensure fields are correctly calculated before importing into QuickSight.
  1. Contact AWS Support:
  • Reach out to AWS support with detailed steps and screenshots (like the one provided) to see if there is a workaround or upcoming feature that could address this limitation. Here are the steps to open a support case. If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf.

By following these steps, you should be able to either find a solution within QuickSight or conclusively document it as a limitation in your POC. If you have further questions or need more detailed assistance, feel free to ask!

Thank you for posting your question on the QuickSight Community. Additionally, please reach out to your account manager to request assistance from an AWS QuickSight Solutions Architect.

1 Like
4

I appreciate you looking into this, @Xclipse!

I’ll be sure to try the above and proceed with the recommended next steps if needed.

Best Regards - Anthony

1 Like
6

Hi @AnthonyN, I am marking this reply as, “Solution,” in case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and please create a new post, so we can look at it from a fresh perspective. (Details on using Arena can be found here - QuickSight Arena)