Calculate with reference value per dimension - Please HELP!

[Anonymous]

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:

Thanks in advance for your help.

Best regards.

Theresa

[#2]

Hi,

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

Here is the Sample to do so,

Test:

Load * INLINE [

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)

LOAD Regions,

  min(Yr) as minyear

Resident Test

group by Regions;

final:

LOAD Regions,

  Yr,

  Sales,

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

Resident Test;

drop Table Test;

check out if it helps you.

Regards,

Ravikant

[Anonymous]

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

[#4]

Hi,

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

Regards,

Gerrit

[Anonymous]

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

[#6]

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

[Anonymous]

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

[#8]

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

[jonathandienst · Accepted solution]

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

[Anonymous]

Hello Jonathan,

thank this worked. I'm happy now 😉

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

Best regards.

Theresa