Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
andreas_koehler
Contributor II

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

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

Tags (1)
1 Solution

Accepted Solutions

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

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))

2 Replies

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

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
Contributor II

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

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