topic Re: FirstSortedValue in Line Chart in QlikView

FirstSortedValue in Line Chart

Fri, 15 Nov 2013 16:53:26 GMT

HI.

I'm trying to use this function to retrieve the firstsortedvalue of revenue expression, in a line chart, with Date and Customer dimensions.

Basically, I want the value to be the first one shown in the chart with respect to the other parameters chosen.

The overall idea here is to divide each month's revenue by the first sorted value's revenue, to achieve % change.

But I can't get the firstsortedvalue to work properly.

Here is my syntax, which clearly has problems because it does not retrieve any data.

=$(vPremiumRevenue) / FirstSortedValue($(vPremiumRevenue), [MonthYear], [Customer Name])

Any idea what I'm doing wrong here?

Thanks!

Re: FirstSortedValue in Line Chart

Fri, 15 Nov 2013 16:56:22 GMT

Actually, it retrieves data, but not anything for the firstsortedvalue. It only retrieves the actual (vPremiumRevenue).

Re: FirstSortedValue in Line Chart

Clever_Anjos — Fri, 15 Nov 2013 17:02:36 GMT

FirstSortedValue expects 3 parameters

1) Value to be sorted

2) Weight of sorting

3) Ranking (1,2,3...)

You´re passing Customer Name as ranking parameter

Another point is if two values share same weight,NULL is returned, you have to choose a unique weight (maybe adding another field)

Re: FirstSortedValue in Line Chart

MK_QSL — Fri, 15 Nov 2013 17:31:21 GMT

your expression looks wrong in terms of FirstSortedValue.

If possible, please attach your file for better solution

Re: FirstSortedValue in Line Chart

Fri, 15 Nov 2013 19:32:39 GMT

Unfortunately I can't attach the file. Work stuff.

I'm really a beginner to this -- just learning set analysis now.

How should I write the expression?

I have two dimensions in the line chart--MonthYear and Customer Name.

And one Expression -- the variable of vPremiumRevenue.

It's very simple right now, just showing each Customer's revenue per month/year

Anything else i could do instead of upload the file?

Re: FirstSortedValue in Line Chart

MK_QSL — Fri, 15 Nov 2013 19:37:15 GMT

What output you want? Can you give one example in excel or table with sample data...

Re: FirstSortedValue in Line Chart

Clever_Anjos — Fri, 15 Nov 2013 19:41:03 GMT

What do you expect? Maybe Max() or Min()?

Re: FirstSortedValue in Line Chart

Clever_Anjos — Fri, 15 Nov 2013 19:41:50 GMT

If you want the first value, TOP($(vPremiumRevenue)) will retrieve it

Re: FirstSortedValue in Line Chart

swuehl — Fri, 15 Nov 2013 19:44:34 GMT

Please have a look at this blog post.

http://qvdesign.wordpress.com/2012/05/09/the-indexed-time-series-chart-the-forgotten-technique-at-least-by-most-qlikview-developers/

Re: FirstSortedValue in Line Chart

Fri, 15 Nov 2013 19:57:08 GMT

QlikView

This demo file has a good example of what I'm looking for.

It's really Revenue % Change over time.

So If the User selects 2013, and Jan-Dec, the chart will use January's Revenue as the firstsortedvalue, and the expression will divide each subsuquent calculation of revenue by that number.

As an example:

ABC, Co

Jan revenue = $150

Feb Revenue = $100

Mar Revenue = $200

So in the line chart, ABC Co. would show 100% for Jan, 67% for Feb, and 133% for March.

Feb and March calculations should be relative to the firstsortedvalue, which is the Jan revenue.

I don't know if i'm explaining this very well. But the linked doc shows it very close to what i need to achieve.

Thanks for the replies.

Re: FirstSortedValue in Line Chart

Fri, 15 Nov 2013 21:59:00 GMT

Here's an Excel example of what I'm trying to achieve.

Imagine here that the user had selected Months of Jan-Jun. This would be the output.

Jan is always 100% (1), while the next months are the % up or down the revenue amount is compared to Jan.

Re: FirstSortedValue in Line Chart

Fri, 15 Nov 2013 22:04:00 GMT

And this would be the output if you selected Feb-Jun.

Re: Re: FirstSortedValue in Line Chart

Clever_Anjos — Sat, 16 Nov 2013 00:36:07 GMT

It´s not elegant, but seems to work,

Check if fits your needs

Re: Re: Re: FirstSortedValue in Line Chart

Fri, 22 Nov 2013 16:13:19 GMT

Firstly, I want to say thanks for the sample. Yours works perfectly.

I haven't been able to replicate it in my environment though.

Not sure why. Below is my script.

My line graph expression:

=Sum(if(Month=MonthYear,$(=vPremiumRevenue)))/sum({<Month={"$(=(min(date(MonthYear)),'MMM-

YYYY'))"}>}$=(vPremiumRevenue))

vPremiumRevenue is a set analysis variable defined as:

=Sum({$<TAMSFeeType = {'TAMSRF'}, CustomerSource={'TAMS'}>}[GP Credit Amount]-[GP Debit Amount]) +

Sum({$<TAMSFeeType = {'TAMSAF'}, CustomerSource={'TAMS'}>}[GP Credit Amount]-[GP Debit Amount])

MonthYear is formatted as follows: Jan-2012

Again, I'm a beginner here, and haven't quite grasped some of the syntax or when to use $ and =.

Hopefully this makes sense and you could guide me in the right direction.

Thanks again.

Re: FirstSortedValue in Line Chart

MK_QSL — Fri, 22 Nov 2013 16:21:57 GMT

Try this

=Sum(if(Month=MonthYear,$(vPremiumRevenue)))/sum({<Month={"$(=(min(date(MonthYear)),'MMM-

YYYY'))"}>}$(vPremiumRevenue))

Re: Re: FirstSortedValue in Line Chart

Fri, 22 Nov 2013 17:13:39 GMT

That didn't work either. The problem seems to be partially in the first statement, where it says if(Month=MonthYear).

Those two fields are formatted differently. If I change it to MonthYear=MonthYear, I get data.

However, i am still unable to obtain the revenue for the first selected MonthYear.

This expression always returns the sum of all of the selected months' revenue, instead of just the first selected month's revenue.

=sum({<MonthYear={"$(=(min(MonthYear),'MMM-

YYYY'))"}>}$(vPremiumRevenue))

It does the same thing if I change the variable at the end to just vPremiumRevenue w/o the $...

Any idea what I'm doing wrong?

Re: FirstSortedValue in Line Chart

MK_QSL — Fri, 22 Nov 2013 17:21:26 GMT

What is the value of Month and MonthYear

Is it 'Jan', 'Feb',

'Jan 2013', 'Feb 2013' ???

then you need to convert MonthYear every time by

=Date(MonthYear, 'MMM')

Re: FirstSortedValue in Line Chart

Mon, 25 Nov 2013 16:09:17 GMT

Michael in Qlikview: TOTAL and AGGR in chart expression

Just wanted to say thanks to everyone who helped me out with this. I finally got a working version for my data model.

I managed to find a thorough explanation and example at the above link, in case anyone else is looking to do this sort of thing.

Thanks again to all.