Question Re: Aggregation node not aggregating the value in Calculation view in Technology Q&A

Aggregation node not aggregating the value in Calculation view

former_member224444 — Sun, 17 Jul 2016 18:34:42 GMT

Hi All,

I am creating a calculation view of type CUBE to combine the results of 2 Analytical View and a Scripted Calc View.

Demand Analytical View(Projection_2):

This gives a demand value based on material and material description

FAD Analytical View(Projectioin_1):

This gives FAD values as per Material and Description

I am using UNION on two analytical view, We can see two separate rows one row each from analytical views FAD and Demand.

I have used Aggregate Node to aggregate that result, But aggregation is not happening. for eg: Material X1234 still has two rows.

So when joining this with Mat Percentage Calc view to take an other attribute and value

Mat percentage Calc View:

Join with Aggregation Node gives two records and repeats PERC value 21 for two records:

Default Aggregation in the Calc View Aggregates 21 and doubles the value to 41 for material X1234 in PERC Column

I am expected the value 21 in PERC. But this is not happening because of Aggregation node is not aggregating the union node results.

Can some one please let me know what is the reason for this weird behavior of Aggregation Node.

Thanks,

Rithika

Re: Aggregation node not aggregating the value in Calculation view

muthuram_shanmugavel2 — Mon, 18 Jul 2016 05:27:09 GMT

Hi Rithika,

In Aggregation_1 Node,

What are the columns you mentioned as Attributes (Add as Output) and

What are the columns you mentioned as Measures ((Ass as Aggregated Column)?

I think you have selected all 6 columns as Attributes.

Select MATNR and MAKTX as Attributes (Add as Output) and

Select YEAR_MINUS_6_DEMAND, YEAR_MINUS_5_DEMAND, YEAR_MINUS_4_DEMAND, NEXT_YR_FAD as Measures ((Ass as Aggregated Column)

Hope It would resolve your issue.

Regards,

Muthuram

Re: Aggregation node not aggregating the value in Calculation view

rindia — Mon, 18 Jul 2016 07:31:58 GMT

Hi Rithika,

Under union node, go to Manage mappings (by right click) and you will observe that is Null is ticked.

That's why you are getting null values. To avoid this, use value zero in Constant Value column as shown below.

Set the value accordingly for all measures.

Regards

Raj Kumar Salla

Re: Aggregation node not aggregating the value in Calculation view

former_member224444 — Mon, 18 Jul 2016 10:32:24 GMT

Hi Muthuram,

Is there a possibility at the Node level we can change the field type to Measure and Attribute. We can only do this on semantics node rite. On semantics it is correctly categorized as Measures for fields YEAR_MINUS_6_DEMAND, YEAR_MINUS_5_DEMAND, YEAR_MINUS_4_DEMAND, NEXT_YR_FAD and Attributes for fields MATNR, MAKTX.

On Aggregation_1 Node output fields properties data type of fields YEAR_MINUS_6_DEMAND, YEAR_MINUS_5_DEMAND, YEAR_MINUS_4_DEMAND, NEXT_YR_FAD is DECIMAL(13,3). But these are coming directly from UNION node by default.

And Attributes has VARCHAR data type, which is also sourced from UNION node by default.

Please let me know if you require screenshot for these from the system.

Thanks,

Rithika

Re: Aggregation node not aggregating the value in Calculation view

muthuram_shanmugavel2 — Mon, 18 Jul 2016 11:10:07 GMT

Hi Rithika,

//Is there a possibility at the Node level we can change the field type to Measure and Attribute. //

Yes we can mention it in aggregation node level.

Please find the screenshot.

Add As Aggregated Column will Aggregate the value @ Intermediate Aggregation node level itself.

Then In Final Semantics Output, you have to mention Attributes and Measures.

In your Aggregation_1 Node also,

If you right click on the Fields, you will get two options.

1. Add As Aggregated Columns and

2. Add to Output.

Select these 4 Fields --> YEAR_MINUS_6_DEMAND, YEAR_MINUS_5_DEMAND, YEAR_MINUS_4_DEMAND, NEXT_YR_FAD as Add As Aggregated Columns and

Select MATNR, MAKTX Fields as Add to Outputs.

So the values will be aggregated in Aggregation_1 Node itself and

Join will be performed with Projection_3.

Regards,

Muthuram

Re: Aggregation node not aggregating the value in Calculation view

former_member224444 — Mon, 18 Jul 2016 19:35:53 GMT

Hi Raj,

Thanks for the reply. I have tried your solution it does'nt work. Anyway got to know from your post how to add column as constant value in UNION.

Regards,

Rithika

Re: Aggregation node not aggregating the value in Calculation view

former_member224444 — Mon, 18 Jul 2016 19:39:37 GMT

Hi Muthuram,

I always wait for your reply . Your solution again solved my problem, you are brilliant . This many days i thought that aggregation node by default aggregates the value. Why does aggregation node needs explicit config on measures to aggregate?? This is a big surprise for me. Anyways a big thanks to you.

Regards,

Rithika

Re: Aggregation node not aggregating the value in Calculation view

muthuram_shanmugavel2 — Tue, 19 Jul 2016 05:29:40 GMT

Hi Rithika,

Happy to know your issue was resolved and

thank you for appreciation .

For your question,

//Why does aggregation node needs explicit config on measures to aggregate??

SELECT MATNR, MAKTX, Sum(YEAR_MINUS_6_DEMAND), Sum(YEAR_MINUS_5_DEMAND), Sum(YEAR_MINUS_4_DEMAND), Sum(NEXT_YR_FAD) From <Union_1>

Group By MATNR, MAKTX

In this above example, you are aggregating 4 Measures based on 2 Attributes.

If you want to Aggregate Measures Based on some Attributes (like SQL Group By Statement),

you need the mention the column Whether Attribute or Measures in Aggregation node.

So Attribute selected as Add to Output and

Measures selected as Add as aggregated columns.

Regards,

Muthuram

Re: Aggregation node not aggregating the value in Calculation view

former_member224444 — Tue, 19 Jul 2016 07:52:44 GMT

Got it. Thank you Muthuram