How to create aggregate measures containing embedded dimensional filter inside aggregate?

Question & Answer
,
1

All, first a couple quick things:

Hi! I’m Jeremy, totally new to the Amazon QuickSight product and Community, and really glad to be here! I come from a long line of Siebel/Oracle analytics (OBIEE) and again, let me just say that I’m really glad to now be in this club… :wink: I’m also at my first re:Invent this week and really hope to meet meet some of you tomorrow morning at the QuickSight meetup!

When coming from any other platform, my first inclination is to look to see how I would do what I was super familiar with before in the old tool/paradigm. Love all that I’ve learned how to do thus far in QuickSight.

My main issue I can’t seem to crack right now is how to create dimensionally filtered/aware measures/aggregations. I’ve been playing with LAC and watched the AWS webcast about pre/post agg, etc, but I still seem to be missing “how do I filter an aggregate for only certain dimension matches?” LAC-A and LAC-W seems to be more about aggregates at the respective dimensional level (which I also need and is helpful); but I don’t see how I use them to filter specifically.

Here’s an example from my old world. It boils down to being able to embed a CASE statement within the aggregate wrapper:

The “logical calc” syntax would look like this:

  • Southern Region Units:
    FILTER( SalesFacts.Units USING Markets.Region = ‘SOUTHERN REGION’)
  • Western Region Units:
    FILTER( SalesFacts.Units USING Markets.Region = ‘WESTERN REGION’)

Where “SalesFacts.Units” above was a measure with SUM agg. So the translated relational SQL generated would render something like (if analyzed by year):

SELECT Year,
SUM(CASE WHEN Region = ‘SOUTHERN REGION’ THEN Units),
SUM(CASE WHEN product = ‘WESTERN REGION’ THEN Units)
FROM physical_table
WHERE Region = ‘SOUTHERN REGION’ OR Region = ‘WESTERN REGION’
GROUP BY year

I’m working with call data. My need is to create a “number of missed calls” (a count of call ID filtered by ‘missed’) and divide that by all calls count (unfiltered). But I keep running into errors in QuickSight calc editor.

I feel like I’m missing something obvious here, and have scoured the docs, videos and this forum for answers, and I’m coming up short on my searches. Can someone please point me to what I should read so I can do my homework on this?

Thank you guys!

Jeremy

2

Welcome, @jeremy! We are so glad you are here—participating in the QuickSight Community and at re:Invent! :slight_smile: Our team looks forward to meeting you and other Community members tomorrow morning at the meetup!

I suspect that many folks are watching the re:Invent keynote now…and anticipate that the community will be circling back to you on your question soon! Thanks for reaching out!

3

hmm for your calculated field can you do

count(ifelse({missed_call_field}=‘Missed’,contact_id,NULL))/count(contact_id)

Or are you asking about dynamically filtering (i.e I want to see missed calls if I click missed calls vs abandon calls when I click abandon?)

For that case I would use a parameter but you the same calculation.

count(ifelse({missed_call_field}=${parameter},contact_id,NULL))/count(contact_id)

Let me know if I am in the right direction or if you are looking for something else. Thanks!

2 Likes
4

Thanks @Max and @Kristin. Max — I’ll be able to finally give this a go here in a bit and will report back. Thanks so much, sir.

1 Like
5

Can you try this?

Total calls (unfiltered) = countover({call id}, , PRE_FILTER)
Missed calls = countOver({call id}, , PRE_AGG)
Missed call rate = {Missed calls}/{Total calls (unfiltered)}

Put “Missed call rate” in your visual (e.g. KPI) and filter the visual by Call status = ‘missed’.
The filter will apply to “Missed calls” but not to “Total calls (unfiltered)” because of the “PRE_FILTER” calculation level.

Edit: The checkboxes are meant to be empty square brackets. They’re getting changed to checkboxes automatically…

1 Like
6

Hi @jeremy. Did the response from @David_Wong or @Max help you? Let us know if this is resolved. And if it is, please help the community by marking the answer as a “Solution”. Many thanks.

1 Like