cancel
Showing results for 
Search instead for 
Did you mean: 

special aggregation in the query

Former Member
0 Kudos

I have two characteristic: characteristic#1 and characteristic#2.

I explain with an example in report.


if there are two strings:


       characteristic#1       characteristic#2     Summ
String_110711023523,123
String_210711071578,123


In the report should appear only String_2.

ie rule - show data in the report, if the characteristic#1 = characteristic#2.

How to set up such a restriction query?

I think to use a special aggregation, but how ??

Respectfully yours, Pavel

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member586947
Contributor
0 Kudos

HI Pavel,

    Is "SUM" a keyfigure in your query. If so, try below steps to get desired output.

1.Create two formula variables with replacement path for the two characteristics.

characteristic#1 --> FMCH1 and characteristic#2 -->FMCH2

2.Create a formula with IF condition by making use of SUM keyfigure. And define the formula as below.

((FMCH#1 == FMCH#2) * SUM) + ((FMCH#1 <> FMCH#2) * SUM)

Try this and let me know.

Regards,

Satya.

Former Member
0 Kudos

Yes SUM is Key Figure.

But your formula not work.

Variable-formula does not pass value.

Error in query - "replacing a numeric variable FMCH#1 and FMCH#2 is not possible".

Your formula is also not true. I need to see only a line characteristic#1 = characteristic#2.

Your example ... + ((FMCH#1 <> FMCH#2) * SUM) add value where characteristic#1 <> characteristic#2. I do not need it.



I guess need to use a special aggregation for calculated keyfigure for each characteristic. But I do not know what.


Regards, Pavel.

former_member586947
Contributor
0 Kudos

Hi,

   Have you tried or not in the system. What is your requirement? Your output should be as below as per your first message. Am I right or not?

String_2  1071 1071 578,123

Have you created formula variables on each of the characteristics?

((FMCH#1 == FMCH#2) * SUM)  is  sufficient in the formula keyfigure. And it is with boolean operators.


IF FMCH#1 equals to  FMCH#2 then SUM displayed in the report else SUM will be zero.


You can suppress the rows with SUM value =0 by defining the condition on newly created formula keyfigure.

Regards,

Satya.

Loed
Active Contributor
0 Kudos

Hi,

Right now you have your CHAR1, CHAR2, and SUM in your COLUMNS section..Hide CHAR1 and CHAR2..

As Satya said, you need to create two (2) formula variables with REPLACEMENT PATH as its processing by and link them to your CHAR1 and CHAR2, let's call them FV1 and FV2, respectively..

Create a formula, let's call it FORMULA1, and insert FV1..

Create another formula, let's call it FORMULA2, and insert FV2..

Create again another formula, let's call it FORMULA_CHECK, and insert this equation:

FORMULA_CHECK = (FORMULA1 = FORMULA2) * 1 + 0

Then activate the CONDITION in your query and make a condition to FORMULA_CHECK = 1..So that all values of FORMULA_CHECK with ZERO will not be shown..

OR

If you do not want to use CONDITION, here is another approach:

Right now you have your CHAR1, CHAR2, and SUM in your COLUMNS section..Hide them all..

.

Create two (2) formula variables with REPLACEMENT PATH as its processing by and link them to your CHAR1 and CHAR2, let's call them FV1 and FV2, respectively..

Create a formula, let's call it FORMULA1, and insert FV1..

Create another formula, let's call it FORMULA2, and insert FV2..

Hide FORMULA1 and FORMULA2..

Create again a formula, let's call it FORMULA_CHAR1, and insert this equation:

FORMULA_CHAR1 = (FORMULA1 = FORMULA2) * FORMULA1 + 0

Create another formula, let's call it FORMULA_CHAR2, and insert this equation:

FORMULA_CHAR2 = (FORMULA1 = FORMULA2) * FORMULA2 + 0

Create another formula, let's call it FORMULA_SUM, and insert this equation:

FORMULA_SUM = (FORMULA1 = FORMULA2) * SUM + 0

Then activate the SUPPRESS ZEROS option in your query..

Just post here for questions..

Regards,

Loed