Former Member

# special aggregation in the query

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_1 1071 1023 523,123 String_2 1071 1071 578,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

10|10000 characters needed characters exceeded

• Aug 19, 2015 at 09:07 PM

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.

10|10000 characters needed characters exceeded
• Loed Despuig Former Member

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