FirstSortedValue in Line Chart

[#1]

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!

[#2]

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.

[#3]

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

[Clever_Anjos]

It´s not elegant, but seems to work,

Check if fits your needs

[#5]

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.

[MK_QSL]

Try this

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

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

[#7]

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?

[MK_QSL]

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

[#9]

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.