Get the dimension value associated to a max expression

[#1]

Hello

I would like to get the dimension value associated to the corresponding expression which is maximal.
I mean, let's say i have in a table:

Country - Date - Business - Sales

And i woud like to have in a chart (or in any other way) , per country, the date which matches the maximum value of sales.
I so created a chart with dimensions:

Country

as expression : ????

Thanks a lot for your help ! 🙂

[#2]

Hi

look at this thread, Martin has shown a way how to accomplish this

http://community.qlik.com/forums/p/28631/109983.aspx#109983

Regards

Juerg

[#3]

WEll, unfortunately, i don't have (yet) a QV licence...
So i can't open the file within the topic....

My new formula is this one :

TABLE :
ElementID
Stat_PFT
Stat_Date
Stat_Nb

Chart elements

- As dimensions:
Stats_PFT
ElementID

-As expression

=if(Stat_Nb = aggr(max(Stat_Nb),Stat_PFT,ElementID,Stat_Date)

Unfortunately this still doesn't work 😞

And i don't really understand the other topic without the QVD 😐

Can anyone help me on this?

[#4]

Hi

I can't recognize the elements you listed in your second post.

You fist talked of County - Date- Business - Sales, now it is

ElementID, Stat_PFT, Stat_Date, Stat_Nb

What do you get then with this formula in your case?

subfield(concat(aggr(rank(sum(total <Stat_Pft,ElementID> Stat_Nb)) & '#' & Stat_Pft & ',' & ElementID & '#)),'#',2)

Regards

Jürg

[#5]

Hi,

Sorry, the meaning of the fields is quite similar.

County -> Stat_PTF
Date-> Stat_Date
Business-> ElementID
Sales -> Stats_NbTraps

I tried your formula but it doesn't seem to be working.... It displays '-'...

Thanks in advance for your help ^^ I'm a bit lost in those "complicated' formulas .... 😞

[#6]

Attached: a file explaining what i would like to have.... i hope it became clearer with it ^^

[#7]

Hi

got the right results with the following formula and your data you provided in the excel sheet, you will need to verify that it works for all your scenarios.

When you get a '-' result in many cases the evaluation of the formula breaks (e.g. wrong spelling, unmatching brackets etc) and it will return NULL, represented as '-'

subfield(concat(aggr(nodistinct rank(sum(Sales)) & '#' & Date & '#',Date)),'#',rank(sum(Sales))*2)

Regards

Juerg

[#8]

Thanks for your Help Juerg,it's perfectly working on the sample i provided you.

In fact, my data are based on some incidents raised by an application.So you don't always have value for a specific date, so the end of your formula doesn't seem to be working (the "rank(sum(Sales))*2)"). I've thought about improving it by replacing the "2" by a Count(Sales)....

Anyway, i'm looking for a max value (but as i have only one value per date, max is similar to sum? ). In fact, for a specific value, the

(aggr(nodistinct rank(sum(Sales)) & '#' & Date & '#',Date)),

doesn't always return the good value.... If U want, i can send you by PM the QVW file...

[#9]

Hi

Thought it might not work. Just a quick question, I see that lots of your live data has Sale=1 for many dates. What is the expected result (which date to show) and do you want to show the max per day or rather per week or month?

Regards

Jürg

[#10 · Accepted solution]

Hi

Another idea but will disregard selected date ranges - evaluating the best selling day while loading the data.

sales:
LOAD Country & Business as %key,
Country,
 Business,
 date(Date) as Date,
 Sales
FROM
D:\BI\QlikView\Examples\Chart_dim_of_max_value.xls
(biff, embedded labels, table is Sheet1$);
best:
LOAD %key
,firstsortedvalue(distinct Date,-Sales) as bestDate
RESIDENT sales
GROUP BY %key;

Then simply add the bestDate into your table

Edit: Looked at the "firstortedvalue" in the load command but it is also available as chart expression. So using it in the chart will give you the best date within your selected date range.

=firstsortedvalue(distinct Date,-Sales)

Regards

Jürg