How to unify some measures into a single measure?

Question & Answer
, , , , , ,
1

Hello

I have a dataset with several summarized and grouped fields, such as qty_coupon, qty_simulation, qty_contract etc.

In Quicksight I need to create a funnel chart, but I can’t put all the fields in measures (only one)

How can I create a calculated field categorizing each stage of the funnel and put only one field in measures?

qty_simulation → simulation
qty_contract → contract
qty_coupon-> coupon

Tks!

2

Hello @July

To create a funnel chart in Amazon QuickSight by categorizing each stage and using a single measure, you can follow these steps:

  1. Prepare Your Dataset: Ensure your dataset has fields such as qty_coupon, qty_simulation, qty_contract, etc.
  2. Create a Calculated Field for Category:
  • Go to your dataset.
  • Create a new calculated field named Stage with the following formula:
ifelse({qty_coupon} > 0, 'Coupon', 
       ifelse({qty_simulation} > 0, 'Simulation', 
              ifelse({qty_contract} > 0, 'Contract', 
                     'Other')))
  1. Create a Calculated Field for Values:
  • Create another calculated field named Value with the following formula:

ifelse({qty_coupon} > 0, {qty_coupon}, 
       ifelse({qty_simulation} > 0, {qty_simulation}, 
              ifelse({qty_contract} > 0, {qty_contract}, 
                     0)))
  1. Create a Funnel Chart:
  • Go to your analysis.
  • Add a new visual and select the funnel chart type.
  • Drag the Stage calculated field to the Group/Color field well.
  • Drag the Value calculated field to the Value field well.

This setup will categorize each stage of the funnel based on the conditions you specified and will allow you to visualize all the stages in a single funnel chart using one measure.

2 Likes
3

Hi Xclipse

Thank you for your support.

I create as according to your example, but it just shows the first stage (first ifelse).

Any ideia?

4

Hi @July

If you are loading data from a database, you can efficiently manage this CASE statement and load the transformed data into QuickSight. However, with the available QuickSight functions, it does not evaluate the second condition once the first condition is satisfied because we use the different field names.

2 Likes