How to calculate percentage

[Anonymous]

Dear all,

I'm new be and I need a little help.

You will find in attachment an EXCEL file with the example of a report I've to produce.

I don't know how to calculate the percentage of a part number on total typology(yellow field) and on total for customer (orange field)

I think I've to use aggr function, but.... I'm not able

Help please

Thank in advance for you support

[sunny_talwar · Accepted solution]

For % of typology, try thus

Sum(Amount)/Sum(TOTAL <Customer, Typology> Amount)

For % on Customer

Sum(Amount)/Sum(TOTAL <Customer> Amount)

[nagarjuna_kotha]

TB1:

LOAD * INLINE [

    Customer, Typology, PartNumber, TotalAmount

    customer1, typology1, 32314-1, "316,032"

    customer1, typology1, 340841-1, "231,694"

    customer1, typology1, 117974-1, "202,305"

    customer1, typology1, 340840-1, "197,223"

    customer1, typology2, 1447-1, "163,248"

    customer1, typology2, 32313-1, "125,838"

    customer1, typology2, 34230-1, "113,576"

    customer1, typology2, 341803-1, "103,068"

    customer1, typology2, 34229-1, "90,984"

    customer1, typology2, 34233-1, "49,840"

    customer2, typology1, 120957-1, "65,039"

    customer2, typology1, 5279-1, "63,584"

    customer2, typology1, 75309-1, "22,121"

    customer2, typology1, 1655-4, "8,870"

    customer2, typology1, 86108-1, "6,944"

    customer2, typology1, 124019-1, "5,321"

    customer2, typology2, 200290-1, "5,204"

    customer2, typology2, 124022-1, "5,116"

    customer2, typology2, 1682-4, "4,211"

    customer2, typology2, 124021-1, "2,911"

    customer2, typology2, 124023-1, "2,881"

    customer2, typology2, 124020-1, 470

];

TB2:

LOAD Customer,Sum(TotalAmount) as Customer_TotalAmount Resident TB1 Group By Customer ;

TB3:

LOAD Customer as Customer,Typology,Sum(TotalAmount) as Typology_TotalAmount Resident TB1 Group By Customer, Typology ;

Expression:

For Typology % : (Sum(TotalAmount)/Sum(Typology_TotalAmount))*100

For Customer % : (Sum(TotalAmount)/Sum(Customer_TotalAmount))*100

[nagarjuna_kotha]

OutPut:

[Anonymous]

It works.

Thanks !!