Aggregation node not aggregating the value in Calculation view

[former_member224444]

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

[muthuram_shanmugavel2 · Accepted solution]

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

[former_member224444 · Accepted solution]

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

[muthuram_shanmugavel2 · Accepted solution]

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

[former_member224444 · Accepted solution]

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

[muthuram_shanmugavel2 · Accepted solution]

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

[former_member224444 · Accepted solution]

Got it. Thank you Muthuram

[rindia]

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

[former_member224444]

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