Returning the most frequent value when this value is not in the fields selected. How?
My appologies if this title is difficult to decypher:
On a dashboard I want to show values irrespective any field selections.
To show the value of the field DISH with the most counts on the second latest day ( aka yesterday) I modified a formula taken from return the most frequent asset.
Let's say that the DISH which has been sold most 'yesterday' is 'Chicken Soup'.
But this suddenly changes, when someone accidentally selects a day when there was no 'Chicken Soup' at all. What happens is that this value, which should have been static, changes its value to the second most frequent DISH -say- 'Noodle soup'.