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

[Ravi_Channe]

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.

[Ravi_Channe]

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

[Ravi_Channe]

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