Aggregated and non aggregated

Question & Answer
1

I have a calculated field that returns a 1 or a 0 depending on a date in a field. (it checks if the date is more current than a hire date and if the field is not null, if both conditions are true, then it outputs a 1 . what i would like to do is then 4 similar fields and divide by another number to determine completion percent. The problem i am running into is the fields are aggregated and wont let me use them in other calculated fields. Is there a way to do this without running afoul?

2

Using PRE_AGG will result in an unaggregated measures.
https://docs.aws.amazon.com/quicksight/latest/user/level-aware-aggregations.html

1 Like
3

When you try to save the calculated field, what syntax/calculation error is it giving you?

4

the first agg field i am using is Module1_completed
ifelse(
{module_id} = ‘#####’
AND {completed_date_utc} >= {day_1_date}
AND isNotNull({completed_date_utc}),
1,
0
)

what i would like to do is take a sum of module1_completed, module2_completed,module3_completed,module4_completed and then divide based on the number of weeks of tenure the employee has. the error i was getting was about aggr and non aggr values

5

part of the problem is there can be multiple rows with the module completed.

6

Okay I think I understand. There are a couple of things to note here.

  1. Order of Operations (PEMDAS)
  2. Aggregation consistency

In order to create a calculated field with aggregated fields in the calculation, ALL fields must be aggregated or not aggregated, you cannot have a mixture of aggs vs nonaggs in the CF.

Your calculation would look something like this (there are multiple ways to do this)

Calculated Field: Mod1CompleteTest
Syntax:
ifelse(module_id = 123456 (no single-quotes needed unless it is a string) AND completed_date_utc >= day_1_date AND isNotNull(completed_date_utc), 1 , 0 )

Repeat this calculation for each module and create a final calculation like this

(Remember the two key points above here)

Calculated Field: finalModAgg
Syntax:
(Mod1CompleteTest+Mod2CompletedTest+Mod3CompletedTest)/(sum(Weeks))

(Make sure your week count is being aggregated in this final calculation).

The other option here is to create flags in your query (which is generally easier because your IDE will tell you what is wrong with your syntax).]

Creating the flags in your query is best done with CTEs using a separate section for each of your module flags so you don’t end up with duplicate employee rows.

1 Like