topic Get the dimension value associated to a max expression in QlikView

Get the dimension value associated to a max expression

Wed, 02 Jun 2010 15:37:21 GMT

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 ! 🙂

Get the dimension value associated to a max expression

Wed, 02 Jun 2010 16:00:31 GMT

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

Get the dimension value associated to a max expression

Fri, 04 Jun 2010 16:01:05 GMT

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?

Get the dimension value associated to a max expression

Mon, 07 Jun 2010 07:18:39 GMT

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

Get the dimension value associated to a max expression

Mon, 07 Jun 2010 15:01:43 GMT

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

Get the dimension value associated to a max expression

Mon, 07 Jun 2010 15:13:16 GMT

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

Get the dimension value associated to a max expression

Mon, 07 Jun 2010 16:58:32 GMT

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

Get the dimension value associated to a max expression

Mon, 07 Jun 2010 19:10:15 GMT

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

Get the dimension value associated to a max expression

Tue, 08 Jun 2010 07:04:03 GMT

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

Get the dimension value associated to a max expression

Tue, 08 Jun 2010 07:30:15 GMT

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

Get the dimension value associated to a max expression

Tue, 08 Jun 2010 13:55:10 GMT

Thanks a lot Jürg, you just saved my life ^^ this works perfectly !!!!

It's perfectly working, i firstly thought about the "firstsortedvalue" but i tried using the other option...

BTW, What does the "%" mean in the loading script ?