Using Calculated Field as a Parameter

Question & Answer
,
1

I’m trying to set up a parameter to create a drop-down of some size groups I created using a calculated field. I’m using the multiple value options because I would like size group name to corresponding with the calculated field.

For example, we have calculated fields for a 26-50 size group and a 11-25 size group. I’d like to set up the parameters, so that when I change the drop down the visualizations correspond.

Here’s what I’ve currently tried:

ifelse(
${ActiveUserSeatCount} = '1 Seat', countIf ({Account UUID},{Active User Seat Count} = 1),
${ActiveUserSeatCount} = '2-5 Seats', countIf({Account UUID}, {Active User Seat Count} <= 5 AND {Active User Seat Count} >= 2),
${ActiveUserSeatCount} = '6-10 Seats', countIf({Account UUID}, {Active User Seat Count} <= 10 AND {Active User Seat Count} >= 6),
${ActiveUserSeatCount} = '11-25 Seats', countIf({Account UUID}, {Active User Seat Count} <= 25 AND {Active User Seat Count} >= 11),
${ActiveUserSeatCount} = '26-50 Seats', countIf({Account UUID}, {Active User Seat Count} <= 50 AND {Active User Seat Count} >= 26),
${ActiveUserSeatCount} = '51-100 Seats', countIf({Account UUID}, {Active User Seat Count} <= 100 AND {Active User Seat Count} >= 51),
${ActiveUserSeatCount} = '100+ Seats', countIf({Account UUID}, {Active User Seat Count} >= 100)
NULL
)
2

Hello @Tj39 , something you could try instead would be an ifelse statement that looks like this:
ifelse(
${ActiveUserSeatCount} = ‘1 Seat’ AND {Active User Seat Count} = 1, count({Account UUID}),
${ActiveUserSeatCount} = ‘2-5 Seats’ AND ({Active User Seat Count} <= 5 AND {Active User Seat Count} >= 2), count({Account UUID}),
etc)

That may give you the result you are looking for.

3

You could also attempt a countOver function so you can utilize PRE_AGG in the calculation.

countOver( ifelse(
${ActiveUserSeatCount} = ‘1 Seat’ AND {Active User Seat Count} = 1, {Account UUID},
${ActiveUserSeatCount} = ‘2-5 Seats’ AND ({Active User Seat Count} <= 5 AND {Active User Seat Count} >= 2), {Account UUID},
…), [Active User Seat Count], PRE_AGG)

4

Thanks for your help. I keep getting syntax errors when I try CountOver and ifelse statements.

countOver( ifelse( ${ActiveUserSeatCount} = ‘1 Seat’ AND {Active User Seat Count} = 1, {Account UUID}, ${ActiveUserSeatCount} = ‘2-5 Seats’ AND ({Active User Seat Count} <= 5 AND {Active User Seat Count} >= 2), {Account UUID}, ${ActiveUserSeatCount} = ‘6-10 Seats’ AND ({Active User Seat Count} <= 10 AND {Active User Seat Count} >= 6), {Account UUID}, ${ActiveUserSeatCount} = ‘11-25 Seats’ AND ({Active User Seat Count} <= 25 AND {Active User Seat Count} >= 11), {Account UUID}, ${ActiveUserSeatCount} = ‘26-50 Seats’ AND ({Active User Seat Count} <= 50 AND {Active User Seat Count} >= 26), {Account UUID}, ${ActiveUserSeatCount} = ‘51-100 Seats’ AND ({Active User Seat Count} <= 100 AND {Active User Seat Count} >= 51), {Account UUID}, [Active User Seat Count], PRE_AGG)

Maybe I’m doing something wrong or not comprehending all the way. Thank you for your help in advance.

5

Hello @Tj39 , I think your issue might be occurring because of the single quotation marks on the values returned from the parameter. Try the calculated field below:
countOver( ifelse( ${ActiveUserSeatCount} = "1 Seat" AND {Active User Seat Count} = 1, {Account UUID}, ${ActiveUserSeatCount} = "2-5 Seats" AND ({Active User Seat Count} <= 5 AND {Active User Seat Count} >= 2), {Account UUID}, ${ActiveUserSeatCount} = "6-10 Seats" AND ({Active User Seat Count} <= 10 AND {Active User Seat Count} >= 6), {Account UUID}, ${ActiveUserSeatCount} = "11-25 Seats" AND ({Active User Seat Count} <= 25 AND {Active User Seat Count} >= 11), {Account UUID}, ${ActiveUserSeatCount} = "26-50 Seats" AND ({Active User Seat Count} <= 50 AND {Active User Seat Count} >= 26), {Account UUID}, ${ActiveUserSeatCount} = "51-100 Seats" AND ({Active User Seat Count} <= 100 AND {Active User Seat Count} >= 51), {Account UUID}, NULL) [Active User Seat Count], PRE_AGG)

I believe that will solve the issue. Let me know how that goes!

6

@Tj39 did my response help you resolve your QuickSight question?

7

@Tj39 I am going to close this topic for now. If you have any more questions feel free to make a new topic in the community! Good luck with your dashboarding!