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.