Find value for min Date using calculated field

Hi wondering how I can go about finding the value for the min date on a dashboard. I want to use it for a comparison in a KPI.

unfortunately quicksight does not support comparisons of aggregated fields against non-aggregated fields so ifelse(date = min(date), adjclose, 0) does not work.

I’ve been using ifelse(date = ${startDate}, adjclose, 0) with a user defined parameter but there are many instances where there is no data for the selected date (startDate). Is there anyway I can get the value for a min(date) that exists in my data?

Two ways I’ve thought about doing it.
coalesce(ifelse(date = now(), adjclose, NULL), ifelse(date = addDateTime(-1, ‘DD’, now()), adjclose, NULL), ifelse(date = addDateTime(-2, ‘DD’, now()), adjclose, NULL), ifelse(date = addDateTime(-3, ‘DD’, now()), adjclose, NULL), ifelse(date = addDateTime(-4, ‘DD’, now()), adjclose, NULL))

This does not return a value even if one of the dates is valid, this is also looking at max date instead of min.

ifelse(date = ${startDate} OR date = addDateTime(1, ‘DD’, ${startDate}) OR date = addDateTime(2, ‘DD’, ${startDate}) OR date = addDateTime(3, ‘DD’, ${startDate}) OR date = addDateTime(4, ‘DD’, ${startDate}) , adjclose, NULL)

This often returns multiple values and I lose the ability to do any type of KPI calculation because of it. I can only do KPI calcs if I use only the first ifelse. This is looking at the min date as defined by a user selected parameter called startDate.

I figure whatever way I do this for min date I should hopefully be able to do the same for max date. Both solutions here do not work though as I get limited functionality with the second solution and no value returned for the first.

Hi @Sean_B

Can you try



1 Like

@Max unfortunately neither worked I just went back an preprocessed the data for static frequencies daily / monthly instead. I’ve mentioned this before on community but I really think there could be a good use for a windowChange / windowDiff function, that preferably can be indexed by not only a integer index but also a date so users can look at change / diff by week (first day in week vs last day) month (first day in month vs last day)… etc. For now I’m just opting to preprocess all of this in my backend which makes things a lot less dynamic but I hope will work for my use case. I guess you could also maybe call it periodToDateChange / periodToDateDiff as it might fit better there naming wise.

With the output not being a single aggregated value but a partially aggregated continuous series, if you have 365 days of data and perform the calculation by week you’d now have 52 data points, by month you’d now have 12 data points, by day you’d have either 365 and 1 NULL or 364.

@Sean_B -
Thank you for your response! I will mark this as a product feature request for calculations.