Returning the most frequent value when this value is not in the fields selected. How?

[andreas_koehler]

Dear all,

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.

=FirstSortedValue(DISH,

-aggr(count(

{1<DaysCont={"$(=Max({1}DaysCont)-1)"}>}

DISH),DISH))

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'.

How do I avoid chicken to become noodles?

Any help is - as always- highly appreciated.

Andreas

[sunny_talwar · Accepted solution]

May be you want to ignore selection in day field?

=FirstSortedValue({<day>}DISH,

-aggr(count(

{1<DaysCont={"$(=Max({1}DaysCont)-1)"}>}

DISH),DISH))

or you may want to ignore all selections?

=FirstSortedValue({1}DISH,

-aggr(count(

{1<DaysCont={"$(=Max({1}DaysCont)-1)"}>}

DISH),DISH))

[andreas_koehler]

You saved my afternoon in 3 min. Impressive. Thanks a lot !