Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

theresaoehler
New Contributor III

Calculate with reference value per dimension - Please HELP!

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.

Unbenannt.JPG

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:

Unbenannt.JPG

Thanks in advance for your help.

Best regards.

Theresa

1 Solution

Accepted Solutions
MVP
MVP

Re: Calculate with reference value per dimension - Please HELP!

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
9 Replies
Not applicable

Re: Calculate with reference value per dimension - Please HELP!

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

theresaoehler
New Contributor III

Re: Calculate with reference value per dimension - Please HELP!

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

Not applicable

Re: Calculate with reference value per dimension - Please HELP!

Hi,

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

Regards,

Gerrit

theresaoehler
New Contributor III

Re: Calculate with reference value per dimension - Please HELP!

Hi Gerrit,

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

Unbenannt.JPG

What can I do that the region will be considered?

Kind regards.

Theresa

Not applicable

Re: Calculate with reference value per dimension - Please HELP!

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

theresaoehler
New Contributor III

Re: Calculate with reference value per dimension - Please HELP!

Hello Ashutosh,

the minimum year is not always 2001, It depends on the selection in the field Year. Like this:

Unbenannt.JPG

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


Not applicable

Re: Calculate with reference value per dimension - Please HELP!

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

MVP
MVP

Re: Calculate with reference value per dimension - Please HELP!

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
theresaoehler
New Contributor III

Re: Calculate with reference value per dimension - Please HELP!

Hello Jonathan,

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

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

Best regards.

Theresa

Community Browser