9 Replies Latest reply: Nov 20, 2013 7:36 AM by Theresa Oehler

Hello everybody,

I have an urgent problem. Maybe it's simple but I tried so many things and still have no solution.

I have the following table: It is an example but similar to my problem.

Now I want the refernce value to be calculated in the fourth column. The reference value is always the Sales value for the minimum year per region. This means that for the region Asia the refernce value should be 11, for Europe 8 and for the USA 10. But as you can see these values just appear where the year is similar to the minimum year and otherwise appears a zero.

What can I do to receive the reference value per region? The result should like the following table:

Best regards.

Theresa

Hi,

You can create the Fourth reference Colunm  in the Script also .

Here is the Sample to do so,

Test:

Regions , Yr , Sales

Asia ,2010,11

Asia ,2011,16

Asia ,2012,20

Europe ,2010,8

Europe ,2011,12

Europe ,2012,11

USA ,2010,10

USA ,2011,15

USA ,2012,20

];

left join(Test)

min(Yr) as minyear

Resident Test

group by Regions;

final:

Yr,

Sales,

if(Yr<>minyear,peek(refsales),Sales) as refsales

Resident Test;

drop Table Test;

check out if it helps you.

Regards,

Ravikant

Hello Ravikant,

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?

Kind Regards

Theresa

Hi,

Try: Sum ({\$<Year={2010}>} TOTAL [Sales])

Regards,

Gerrit

Hi Gerrit,

I've also tried this. But the result is the following:

What can I do that the region will be considered?

Kind regards.

Theresa

Hi,

If you know that what is  Minimum year (like 2004) then you can directly  use this in straight table expression.

=If(Year = 2004,Sales)

And  if it is not helpful then let me clear one thing why are you taking selected Year-Range (2004-2012) if starting year is 2001.

Regards,

Ashutosh

Hello Ashutosh,

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?

Kind regards.

Theresa

Hi,

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,

Ashutosh

Theresa

I think you could use something like this:

=Min(TOTAL <Region> Aggr(Sum(Sales), Region, Year))

assuming your chart dimensions are Region and Year.

HTH

Jonathan

Hello Jonathan,

thank this worked. I'm happy now ;-)

But thanks to all the others who tried to solve my problem.

Best regards.

Theresa