Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
andreas_koehler
Creator II
Creator 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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

2 Replies
sunny_talwar

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
Creator II
Creator II
Author

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