Aggregate data in Calculation View

[Former Member]

Hello,

I was trying to create a create a calculation view with aggregated data, for example Car dealers and the total number of cars sold in a month.

So I need to group by dealer and get the count of cars that are sold.

I want to create a graphical calculation view. When I use aggregation node I can see only sum, max & min.

How can I get count or avg function?

Expecting a response.

Much Thanks,

Indu

[former_member184768]

Hi Indu,

You can use the "Counter" in Calc view. As per the modeling guide for HANA:

Counters add a new measure to the calculation view definition to count the recurrence of an attribute. For example, to count how many times product appears.

Create counters to obtain the number of distinct values of an attribute (optional)

a) Select the default aggregation node.

b) In the Output pane, right-click Calculated Columns.

c) From the context menu, choose New Counter. Enter a name and description (label).

d) To hide the counter during data preview, select the Hidden checkbox.

e) To add attributes in the Counters panel, choose Add and choose OK.

Remember

You can only create counters on the default aggregation node.

Please refer to the documentation for further details.

This can be used in your case to calculate the average (sum(KF) / count(KF))

Regards,

Ravi

[ranjit_alapati]

Hi Indu,

I hope you got the answers from the post from Ravi.

Best Regards,
Ranjit

[Former Member]

Hi Ravi,

As you mentioned, why do we need to use aggregated node while using counters as counters can be used without aggregated node.

Secondly, if I use a aggregated node and then while creating a counter what shud be selected in the attributes, Measures and the attributes within the counter?

Hope you would have understood.

[former_member184768]

Hi Indu,

As I mentioned, explicit aggregate node is not required. If your Calc view is being used for Multi Dimensional reporting, by default the behavior of Calc view is to AGGREGATE the data.

Secondly the counter works something like Exception aggregation in BW BEx queries. Here you need to select the counter by a given attribute. If you select dealer, then it will be COUNT(Dealers). If you want to count the number of complaints then you need to select the complaint no in the counter. But prior to that you need to select the complaint no in the output.

Then you can fire a query against the Calc view with DEALER and TOTALCOMPLAINTS in the select statement.

Regards,

Ravi

[Former Member]

Hi Ravi,

Tried 2 methods:

Method 1: Added both Dealer & complaints in attributes and created a counter for complaints.

Method 2: Added Dealer to attributes and complaint to measures, Then while creating a counter I was not able to add complaint only dealer was visible, so went ahead and created a counter for dealer .

In both the methods, while validating the model it was throwing an error saying:

Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Create Scenario: failed aCalcEngine.createScenario(): The following errors occured: Inconsistent calculation model

Thanks,

Indu

[former_member184768]

Hi Indu,

I am on rev 60 (SPS 06), Please find the screenshots of my model and results of the counter (Count of Products). It works for me as expected.

Data model:

Counter setting:

Base data:

Counter data:

Regards,

Ravi

[Former Member]

Hi Ravi,

Appreciate your efforts in uploading the screen shots and giving a step wise execution.

Tried the same, However I am getting an column store error. Screen shots provided below.

Thanks,

Indu

[ranjit_alapati]

Hi Indu,

It is not possible to calculate average using the aggregate function. you can do it by defining the average as a calculated column.

For count you can use the counter option, which allows to count distinct rows based on certain columns.

Best Regards,
Ranjit

[Former Member]

Thanks Ranjit for the response, But wat I don't understand is while creating the counters do we need to add a aggregation node?

Or just create the dealer attribute and create a counter for the total number of cars sold?

When I tried without the aggregation node, I don't see any data in the data preview.

I have added dealer & cars sold in the output columns & created a counter with cars sold.

But not working!

[former_member184768]

Hi Indu,

Which revision of HANA are you currently using. In SPS06 rev 60, the aggregation node is automatically added, but prior to rev 60, it is not mandatory to add EXPLICIT aggregation node.

You can check the output node and create the counter there.

Secondly when you mentioned, that the data preview did not show any data, was there any error. Can you please check the SQL generated (View log, check the SQL generated).

Please add some screenshots to understand the model you created and the SQL statement.

Regards,

Ravi

[Former Member]

Ravi,

I am using SPS04. I have attached the screen shots of the model.

I have created Dealer as a output attribute.  Now I want the total number of complaints for a dealer.

So while trying to create a counter for total number of complaints I can see only dealer as the attribute in the create counter view.

Thanks,

Indu