Aggregation functions not providing expected results in claculation view

[rohit_chowdhury17]

Hi Experts,

Need your expertise to clear my doubts regarding aggregation node in calculation view .

I have a dataset which is at item level .Need to get the unique records from it by aggregating the base records. Need to get the count of some intermediate attribute and then find MAX of the count of the intermediate value. This needs to be done at 3 steps.

1. Aggregate the base set by selecting only those records which are required for next level.

2. Out the records obtained in step 1 create a calculated column to obtain to obtain an attribute and then find the number of times (count )the attribute appears in the dataset obtained in step1 .

3.Use the result set obtained in step 2 to find the record whose count (obtained in previous step)values is MAX

The issue is I am able to obtain the proper aggregation in step 1 and count in step 2 but when I perform max operation of the count value in a second calculation view , the view returns sum of count

The step by step screenshot along with the result of that node is attached.

Can you please comment if the output appearing as shown in step 3 is correct and what i am expecting (attached in the screenshot ) needs to be obtained by some other means .

The DB version is

1.00.122.07.1486663129

Studio Version: 2.3.13

Best Regards

Rohit

[Former Member]

Do you have to retrive only 1 row that is the top value of calculated value?

[rohit_chowdhury17]

Yes , I require a single row in , where the value of the count column is maximum as shown in the screenshot CV_2_expected_op.

The input would be result as shown in screenshot op_cv1 ie

MANDT =310

GPART=x

VKONT=y

CC_COUNT=3

Best Regards

Rohit

[Former Member · Accepted solution]

Hi Rohit,

1. Generate a dummy calculated column below rank block that is equal to 'A' (CHAR 1)

2. Use rank, order by count descending, group by dummy
3. Set threshold (fixed) to 1

Example tutorial:

https://www.youtube.com/watch?v=yA3qaTVSFVg

Regards,
Mateusz.

[rohit_chowdhury17 · Accepted solution]

Thanks a lot Mateusz :),

I am now able to get the MAX of CC_COUNT using the Rank technique you just suggested . But I still have few questions

1. When I tried to see the visualize plan for the projection1 for second CV , I see that all the columns which there in generating the DISTINCT COUNTER of first CV is present (even though the column AUGDT and FAEDN is hidden ). I am asking this because if I dont choose to use a Rank node node and use a MAX , MIN function provided in the aggregation node it should work as well but strangely it isnt .

2. Another question is related to distinct counters

in the first cv output of aggregation 1 is

If I select MANDT , GPART , VKONT,sum(BETRW), sum(CC_FREQ) the out put should be

But instead the result of sum(CC_FREQ) is 1 for the entire data set .

I am asking this because I dont want to use a counter , instead I want to use a aggregated column to do the work .

3. Regarding the concept of Transparent filter :- Any column which takes part in the distinct counter calculation cannot be set as transparent filter, even though it has been hidden in the semantics after counter calculation .But if I dont want that hidden attribute to take part in further aggregation if the base calculation view is called from a different view.

Can you please throw some light on these as well so that I can have correct understanding of these features .

Thanks once again .

Best Regards

Rohit

[Former Member · Accepted solution]

Hi,
I feel like I'm misinterpreting something, but here is what I think:
1. Aggregation in graphical calculation view are grouped by everything that is in the output but it is not aggregated (be sure that measures are aggregated and they are not in output as characteristics). Check SQL group by for more information. For every characteristic (that is used in group by) is equal then the aggregation
2. What do you have in group by? The screen from output tab would be great.
3. Check the blog linked below

https://blogs.sap.com/2015/10/26/getting-the-counters-right-with-stacked-calculation-views/

Regards,
Mateusz.

[rohit_chowdhury17 · Accepted solution]

inputdata.jpg

Hi Mateusz,

That was also my understanding that in aggregation the measure is aggregated on basis of the attribute columns which are in the o/p of that node (be it semantics or intermediate node ) . But with this current data set its not behaving the same .

For example consider the dtable ata set which has been obtained after single level of aggregating an item table along which some filters on AUGDT and FAEDN column .

inputdata.jpg(41.8 kB)

Now I want to aggregate the data set find count of CC_CD on the base of MANDT, GPART and VKONT ,so that the data set should appear to be

expectedresult.jpg

I tried doing it by inputting the previous data set to an aggregation node taking MANDT , GPART , VKONT , CC_CD as attribute and summing it against DUMMY .But the result is 1 in the column sum(DUMMY).

I also tried by adding the column CC_CD as aggregated column (setting its property as COUNT) , still the count comes as 1 .

I am not getting the reason why in the aggregation AUGDT and FAEDN are taking part though I have not taken them in aggregation .

Thanks once again .

Best Regards

Rohit

[Former Member · Accepted solution]

Hi Rohit,

Check the field "keep flag", if it's set then it forces to retrive those in aggregation.

Regards,
Mateusz.

[rohit_chowdhury17 · Accepted solution]

Hi Mateusz,

I have set keep flag in any of the columns .

BR

Rohit

[BenedictV]

Hi Rohit, How does the data look like when you preview it from Projection1 on TEST_2. My guess is since you are using the TEST_1 in the projection.The optimzer ignores the projection node and passes all values to the aggregation node which calculates MAX for each line and the adds them up. Again, I am guessing 🙂 You can check it if you visualize the plan.

After-all, you dont need two separate calc. views to accomplish this. Include one more aggregation node in your first view and calculate the MAX in the final node there.

[rohit_chowdhury17]

Hi Benidict ,

Thanks for spending time and putting you effort on the issue .

Data Preview from projection 1 shows the aggregated of all the rows

Though while checking the explain plan for the statement

SELECT * FROM "_SYS_BIC"."ROHIT_TEST/CC_FREQUENT_PAD_V2/dp/Projection_1"
WHERE ("GPART" = '28');

Three rows are estimated from cv_1 to cv_2 though it should be more than that .Also all the unwanted columns ie AUGDT and FAEDN are also taking part in the the projection though they are not requested and distinct operation is done on them .

Also attached complete explain plan below .explain-plan.png

As suggested by you I also tried doing that in a single calculation view as well . But in this case I am unable to get the count of the record set at the intermediate level by using a aggregation node .As a last resort I found the count in using a counter in the semantics of the first cv .

Steps followed when tring using a single calculation view as suggested :-

1. Keeping only those columns which are required for grouping and aggregating against a calculated measure (i.e 1 ), it gives us the distinct the count of grouped record set .You can see that in the aggregation -1-op screenshot .But then the problem appears , when I try the further aggregate the above result set , group byed by MANDT,GPART,VKONT and setting count(CC_CD ) , I get the count as 1 .

I tried another approach , by creating a calculated measure (ie 1) and summing it against

MANDT,GPART,VKONT,CC_CD still the sum(calculated measure ) returns 1 .

Whatever I do , the node brings all the underlying column into group by clause even if it is not used.

Looking forward for further insights .

Best Regards

Rohit

[former_member200930]

Hi Rohit,

Please check enclosed screenshot and do necessary changes in RANK node. Please select proper partition by columns. I hope, this will solve your problem.

[rohit_chowdhury17]

Thanks Vikram for the suggestion ,

I did the what you suggested , but still the data set is providing incorrect O/P . The iput data set by grouped or partitioned by MANDT , GPART and VKONT where it was ordered by CC_COUNT .

Best Regards

Rohit

[rohit_chowdhury17]

Hi Mateusz,

And thanks a lot for the effort you have been putting 🙂

I tried with a rank node in the 2nd CV . Over here in the rank node i took MANDT , GPART and VKONT as partition and order by as CC_COUNT . In this case the node is aggregating the CC_COUNT as sum even though I am not explicitly aggregating the set and final data set is appearing as pasted in screen shot.

Pls consider 28 , same as 0000000028 .

Best Regards

Rohit

[Former Member]

Dear Rohit,

use Rank, not aggregation in the last step.

Regards,
Mateusz.