You can create the Fourth reference Colunm in the Script also .
Here is the Sample to do so,
Load * INLINE [
Regions , Yr , Sales
min(Yr) as minyear
group by Regions;
if(Yr<>minyear,peek(refsales),Sales) as refsales
drop Table Test;
check out if it helps you.
thank you for your answer. I know that this is a solution but I've forgot to say that the mininmum year is a selected value in the dashboard. In my real case i have the years from 2001 up to 2013. And according to the selected Year-Range (2004-2012) is the minimum year 2004.
Any other ideas?
the minimum year is not always 2001, It depends on the selection in the field Year. Like this:
In that case the minimum year is 2004. I think that the way Gerrit suggested is the right direction to the solution. But I need to do it by concidering the dimension region. So that the result in the fourth column is 11 when the region is Asia and 8 when the region is Europe.
Do you know what I mean?
yes now I understand your problem. Assign this expression in a variable.
let varYear = Min(SubField(GetFieldSelections(Yr), ','));
and then use my script using this variable.
=If(Year = $(varYear),Sales)
or check this attach file.
Thanks and Regards,
ABC.qvw 149.0 K