Incorrect result in Aggregate Function.

[Former Member]

Hello Folks,

We are using aggregation function in an calculation view which is build on an analytical view using sql script which is as follows -:

    var_out = select count(*) as "Notif_Id","Fiscal_Year_Week_Name"
   from "_SYS_BIC"."system/AN_WARRENTY"
   group by  "Fiscal_Year_Week_Name";

which is giving result as -:

But when we check result in analytical view for confirmation it gives the following result -:

These are the distinct values in analytical view,whereas since data is same for both the views we should get the same result (Refer picture 1 and picture 2 ) while running both the views but we are getting the different result.

For FY08W30 and FY08W01 we have Duplicate Notif_Id but in when we run the calculation view it gives FY08W25 as Count 2.

But we want result as FY08W30 = 2 and FY08W01 = 2. which we are not getting.

Please help where we are going wrong.

Thanks

Ashutosh

[lbreddemann · Accepted solution]

Hey Ashutosh,

please make sure to get the SQL of the data preview ( the blue [i] button ) and run the SQL yourself.

Unfortunately the data preview generates some funny nested SQL that will change the grouping of data and thereby the aggregated values.

- Lars

[Former Member]

Hi,

Thank a lot for your help ,it works now

regards

ashutosh

[former_member184768]

Hi Ashutosh,

Please refer to SAP Note 1791464 - Select count(*) on graphical Calculation Views. Please check if this is relevant to you. As mentioned in the note, please check if you have row.count column in the Analytic view which you can use in your calc view.

Regards,

Ravi