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

Calculate with reference value per dimension - Please HELP!

Anonymous — Wed, 20 Nov 2013 10:03:57 GMT

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

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

Wed, 20 Nov 2013 10:45:42 GMT

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

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

Anonymous — Wed, 20 Nov 2013 11:38:26 GMT

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

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

Wed, 20 Nov 2013 11:55:50 GMT

Hi,

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

Regards,

Gerrit

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

Anonymous — Wed, 20 Nov 2013 12:03:44 GMT

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

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

Wed, 20 Nov 2013 12:13:00 GMT

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

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

Anonymous — Wed, 20 Nov 2013 12:22:30 GMT

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

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

Wed, 20 Nov 2013 12:27:47 GMT

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

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

jonathandienst — Wed, 20 Nov 2013 12:28:39 GMT

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

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

Anonymous — Wed, 20 Nov 2013 12:36:12 GMT

Hello Jonathan,

thank this worked. I'm happy now 😉

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

Best regards.

Theresa