Hi! I am trying to make different calculated fields in which all of them I do a comparison between a calculated field called LastDate in which my idea is make a formula that returns me the maximum or the most recent value of different dates of a column called DatePeriod (This is the formula that I used: maxOver(max({DatePeriod}), ), I did not used the formula max(DatePeriod) because it returns me the same date of DatePeriod for of each register instead of the maximum date from all which is that I need).
With that I had created a couple of formulas but they did not work.
For example I made this one: ifelse({LastDate}={DatePeriod},{sum}, 0).
And it shows this error: “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination”.
I checked the documentation and it says that is not possible to make this type of comparison between a custom aggregation and nonaggregated fields, so I have the question if anyone knows an alternative form to do that comparison that I need for my calculated fields. Thank you!
What might help is changing up the ifelse statement.
In the ifelse statement above, is {sum} also an aggregated field? If so are you doing a something like sum{sales} because you could break that out to just {sales} and let the field well aggregate the calculation.
You could also try taking the maxOver function out in someway. For example
You are right, I forgot to specify that Sum is not an aggregated field. I tried the solution but it shows me the same error (Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.) in the second DatePeriod. Personally I do not know how to solve it.
I already tried that formula, but there are two problems. First, that formula returns me exactly the same date of DatePeriod (not he maxium date of all the column) and that is why I use the formula maxOver instead which returns me the maxium value (I do not know exactly why it happens with just the max formula). The second problem is that if I use just the sentence max({DatePeriod}) (even if it is in the same calculated field or in a different) it still shows me the same error of “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination”. DatePeriod is not a calculated field so it keeps making a comparison between an aggregated and nonaggregated field.
I found a possible solution, but it is not so much practical. If the problem is the different “type” of fields (aggregated and nonaggregated) I have made with the formula max(DatePeriod) that returns me the same value of DatePeriod of each row in a different calculated field called DatePeriod(aggregation). So technically I am comparing two aggregated and it helps to solve the main problem of comparison of the last date with other dates in QuickSight. However, is not practical in other cases (just if you want only to make the comparison), for example in the same formula the field sum is not an aggregated, so I will need to use the same trick to use max() to make it an aggregated field.
There is another error that says “Nesting aggregate functions Like (…) is not allowed” when you use another aggregated function in other calculated field (for example AVG) with for example the fields aggregated that I have made with that formula to compare the dates. So as I said, technically it resolves the main problem, but is not very helpful in some cases. I do not know how to works exactly QuickSight and why there are this kind of restrictions, but if there is no problem it would great to make exceptions for this kind of rules.
I think I don’t totally understand the end result. What is the ultimate outcome that you are looking for from this calculated field and what is your use case?
Do you just want this calculated field to show you the largest date in your dataset so that you can use it to make period over period comparisons?
Were you able to find a solution for this and if so could you post it to help the community?
It has been some time since we have heard from you but would still like to help you find a solution. If we do not hear from you in 3 days this post will be archived.