Conditional min for each group

Question & Answer
, ,
1

Refer to the attachment(dummy data)

Screen Shot 2022-02-22 at 12.13.26 PM
Screen Shot 2022-02-22 at 12.13.26 PM2120×1342 375 KB

For each group, I need to create a conditional min of column (log date)when
the (cum users count) for each group is more than 50.

For example: for customer1, on 8/5/21 we see that “cum users count” have exceeded 50, so I need a column (“Live date”) as 8/5/21.

i tried creating a column with minOver and minIf function, but there is an issue of aggregated and non aggregated field. Is it posiible to get the column “Live date” from calculations in Quicksight?

2

Should be doable with minOver() and an if statement to only consider dates that meet the criteria, as follows:

minOver(ifelse({cum users count} > 50, logDate, NULL), [{Group}], PRE_AGG)
3

Thanks Darcoli, for your response. but I have tried this and there is an error with this formula.
“cum users count” is aggregated field while log date is not.

4

Can you please show me your existing definition for cum users count?

5

Ok I suppose cum users count is some runningCount calculation.

In that case, you can calculate Live date as follows

minOver(
  ifelse({cumulative users count} > 50 , min({log date}), null)
  , [Group]
)
1 Like
6

I was wondering if you can help me with a similar question. I am trying to do the same in my environment but I get:

Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

My calculated filed is like following:

minOver(ifelse(FirstId=${PFirstId},min(Id),NULL),[PatientId])