We are looking to continuously calculate a maximum over a field per partition (multiple dimensions), however not for us in a table calculation but rather to be used on every row. Is there a possibility of doing this in Quicksight currently.
Basically what I see is that it requires the ability to refer in a conditional expression to the row (level) for which the value is currently being calculated.
Identical to what you would do in excel with the following example:
(note that the @ [@[Dimension 1 ]] in refers to the value of the current row being calculated whereas [[Dimension 1]] refers to the entire column. “<=”&[@[Dimension 2]] is just the way of phrasing the condition applied to Dimension 2 meaning it has to be less than the value of Dimension 2 for the row being calculated.
Any input would be greatly appreciated. Perhaps there is a more “quicksighty” way of doing this.
Hello @DanielJansson, I think something that could be helpful for you to get your expect result would be the maxOver() function in QuickSight. Do you mind clarifying a bit more on the Output you are expecting? I might be able to put together an example expression for you, but I don’t think I fully understand what the conditions are that you are implementing.
Hello @DanielJansson, do you mind following up with the information I requested so I can help guide you towards a solution? Otherwise, if the maxOver function worked or you were able to resolve the issue, would you be able to post the steps you took to find your solution?
I’m not sure I can describe it much better than I did in the previous example. The purpose is measuring the value against historical values that happened before the current row. As I gather based on testing it’s not possible using the maxOver function.
In practice this could be useful if you are e.g. looking to compare the current value against historical max values for the same partition to identify outliers/anomalies. An example us case would be a measuring activity in a scope and see if it either drops or becomes much higher than the historical max values when you have a continuing increase in activity.
Currently we are instead fixing it outside of Quicksight and then feeding it in on row level as raw-data.
Added:
Actually maybe the following table would provide some more nuance. This becomes very simplified and often the case is not this easy but imagine that you want to compare the bold-marked rows against the historical max to calculate the distance, in these cases 5/6ths and 3/4ths respectively.
