topic Referencing Expression in AGGR in QlikView

Referencing Expression in AGGR

Wed, 30 Oct 2013 19:58:00 GMT

Help Please!!!

I am trying to do basically a vlookup in Excel from the results of 1 column of a company to another inside a straight table.

The following test expression works:

max(total aggr(if(rank(sum([TotalP&L]),1,1)>=60, sum([TotalP&L])),CompanyName))

Where I have randomly entered '60' as a rank to match to... as expected it returns the same sum([TotalP&L]) for all rows of the table.

What I would really like to do is change that 60 to the results of another expression in that table.

I would think something like the below would work, but no luck.

max(total aggr(if(rank(sum([TotalP&L]),1,1)>=[OtherExpression], sum([TotalP&L])),CompanyName))

Re: Referencing Expression in AGGR

rustyfishbones — Wed, 30 Oct 2013 22:45:15 GMT

do you have some sample data

Re: Referencing Expression in AGGR

Thu, 31 Oct 2013 12:31:41 GMT

Sure, I cannot provide my .qvd, but the below is a watered down example.

Say you have customers and you want to look at the time spent with them vs the $ they spend and you have a straight table that looks like:

Customer Name	Total Purchases	Purchases Rank	Total Time Spent (m)	Time Spent Rank	*Time Spent $ Lookup*
Customer1	$150	6	30	7	$100
Customer2	$75	9	35	6	$150
Customer3	$200	5	15	10	$50
Customer4	$50	10	20	9	$75
Customer5	$300	2	25	8	$90
Customer6	$375	1	40	5	$200
Customer7	$250	3	60	2	$300
Customer8	$225	4	90	1	$375
Customer9	$100	7	55	3	$250
Customer10	$90	8	50	4	$225

(In the above chart example only the 'Customer Name' field is a dimension. The others are all calculated and need to be calculated, due to the users's need to adjust date ranges and customer types the chart has to be dynamic.)

The highlighted column is the one that is giving me issues. I want to be able to say Customer8 takes the #1 amount of time, which corresponds to spending $375 (based on our #1 spender).

From my example formula above translating it into this chart:

max(total aggr(if(rank(sum([Purchases]),1,1)>=1, sum([Purchases])),[Customer Name])) will return $375

The question is how to translate that hard coded 1 to the [Time Spent Rank] expression to correctly return the corresponding [Total Purchases] of that [Time Spent Rank].

This is so simple in Excel, but it has given me a real tough time in QlikView. Thank you in advance for any help!

Re: Referencing Expression in AGGR

Fri, 02 May 2014 18:19:37 GMT

Anyone have any thoughts on this? Still an outstanding issue for me.

Re: Re: Referencing Expression in AGGR

nagaiank — Fri, 02 May 2014 20:26:47 GMT

An implementation using script is attached. You may follow similar process for your actual project.

HTH.

Re: Re: Referencing Expression in AGGR

Fri, 02 May 2014 20:33:05 GMT

Thank you for taking the time to respond.

I know how to do this in the load script, I need the ability to implement it in a chart object so the users can select date ranges as well as customer groups.

Re: Re: Referencing Expression in AGGR

rwunderlich — Fri, 02 May 2014 22:25:27 GMT

Attached is a real kludgy solution. It requires that the chart be sorted by Purchases Rank for which I've created a hidden column. I then used the top() function to reference a specific row.

=top(TOTAL

sum([Total Purchases])

,rank(TOTAL sum([Total Time Spent (m)]))

)

I'm sure (hope) there's a better way.

-Rob