Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
jonathandienst
Partner - Champion III
Partner - Champion III

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

View solution in original post

9 Replies
Not applicable
Author

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
Not applicable
Author

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
Author

Hi,

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

Regards,

Gerrit

Anonymous
Not applicable
Author

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
Author

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
Not applicable
Author

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
Author

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
Partner - Champion III
Partner - Champion III

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
Anonymous
Not applicable
Author

Hello Jonathan,

thank this worked. I'm happy now 😉

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

Best regards.

Theresa