Different values in results for characteristics with query designer

[Former Member]

Hi Gurus,

when I have different characteristics like for example:

1) 0C_CTR_AREA

2) 0COMP_CODE

3) 0CUSTOMER

4) 0RISC

5) 0INDEX

for number 3, 4 and 5 I want as a result the last value (key figure setting in query designer "last value" > works).

But for 1 and 2 I want the summation for this key figure.

When the key figure was adjusted with "last value" in query designer, is it possible to assign in some way the summation to number 1 and 2?

Right now it shows for 1 and 2 also the "last value" as result.

Thanks in advance!

[Former Member]

I think there's a workaround for this.

For the three characteristics you want to show the last value of, create a replacement key figure that you can use in a formula. This way, you have the key values of those characteristics in a key figure so you can create a condition only to show the last value. Hide the three colums when you're done.

To make sure the other key figures show the result of all different values of that characteristic in one line, you can use a constant selection on those key figures.

Let me know if it works or of you need any more details about this solution.

Kind regards,

Alex

[Former Member]

Hello Alex,

I have never heard before of a replacement key figure. Is that a standard key figure like "restricted key figure" and "calculated key figure" or a key figure which is called like that and known for a workaround?

Thanks in advance!

[Former Member]

I'm sorry, I meant a key figure with a formula variable based on a replacement path. To use this, you first create a formula and within that formula you create a formulavariable. Choose type replacement path and select for example the key of 0CUSTOMER to be the replacement value.

You now have a column with the customer number in it, like e key figure value. This way you can use a condition to only display the highest value for each 0C_CTR_AREA and 0COMP_CODE combination.

Does this help you?

Kind regards,

Alex

[Former Member]

I wasn´t able to get a solution with your suggestion.

But I think I did it another way.

For one key figure it works I think.

I will post my solution below.

[Former Member]

Hi,

As I know you can individually put restriction on each of the basic key figure. Simply go to properties of that key figure from context menu and set the option "Calculate Single Value As", you can set here the optoin as sum.

Regards,

Durgesh.

[Former Member]

I mean this example is for one key figure with different characteristics.

This one key figure has the property "last value".

The problem is that I need a summation on two other characteristics for this one key figure.

[Former Member]

Hi,

Create a simple formula variable based on this key figure and then from properties set the option as suggested above. Try this approach it might work.

Regards,

Durgesh.

[Former Member]

Hi,

I think you have to create RKF for 3 , 4 and 5 to apply last value.

after that create a new RKF for 1 and 2 and use the Exception aggregation.

Regards,

Ranganath.

[Former Member]

I did it like this:

created one restricted key figure for this ONE key figure (constant selection "=") with properties "calculation result as last value".

Then one new formula with this restricted key figure and with exception aggregation "last value" and characteristic "0calmonth".

Now it shows me the result correct for each characteristic. If I remove one characteristic I think the calculation is also correct.

I have only a few data sets currently but I will test it in detail.

[Former Member]

But for the last characteristic 0C_CTR_AREA when this is the first column in query it does a summation of the last value of 0Comp_Code.

I think this is normal behavior because which one of the - for example two credit control areas like 1000 and 2000 - should be selected as "last value" for result.

There are two values which are correct 1000 with 1 Euro and 2000 with 2 Euros.

But as a result the summation is wrong.

Maybe it is possible to deal with two characteristics. One Standard 0C_CTR_AREA and one new created 0C_CTR_AREA_2 and then there could be presented the last value for each?