Page History

Sv translation

language	en

There are times that the data set only contains the unit price and sold quantity without the calculated revenue. This tutorial will show you how to calculate the revenue and revenue percentage towards total revenue.

Note
It is recommended to get familiar with memberSum function before proceeding with this tutorial.

The table we are going to build will have two drill down levels, Product Group and Product Name. Here is how the table will look like.

ilupng
revenue

Note
Make sure you are displaying the Unit Price correctly. The members aggregation should be Average rather than Sum.

The Revenue, which equals to Unit Price times Quantity is calculated using membersSum,

Code Block


int totalRevenue = 0;

membersSum('[L_PRODUCT_GROUP][L_PRODUCT_NAME][L_TRANSACTION_ID]') {
    int thisRevenue = M_QUANTITY * M_USUAL_PRICE@AVG;
    totalRevenue = totalRevenue + thisRevenue;     
}

return totalRevenue;

Make sure you place the order of the drill down levels in the parameters of membersSum correctly. It should follow the order of the drill downs in the table, so Product Group first and then Product Name. The last drill down level Transaction ID will make sure it aggregates total revenue for all transactions belongs to the same product group and same product.

In order to calculate the revenue percentage, we need to calculate the total revenue first.

Code Block


aggregatePrevLevel(2){ 
int totalRevenue = 0;

membersSum('[L_PRODUCT_GROUP][L_PRODUCT_NAME][L_TRANSACTION_ID]') {
    int thisRevenue = M_QUANTITY * M_USUAL_PRICE@AVG;
    totalRevenue = totalRevenue  +  thisRevenue;     
}

return totalRevenue;
}

So the code for calculating the revenue percentage is as below:

Code Block


int total = aggregatePrevLevel(2){ 

int totalRevenue = 0;

membersSum('[L_PRODUCT_GROUP][L_PRODUCT_NAME][L_TRANSACTION_ID]') {
	int thisRevenue = M_QUANTITY * M_USUAL_PRICE@AVG;
    totalRevenue = totalRevenue  +  thisRevenue;     
}

return totalRevenue;
}

int totalRevenue = 0;

membersSum('[L_PRODUCT_GROUP][L_PRODUCT_NAME][L_TRANSACTION_ID]') {
	int thisRevenue = M_QUANTITY * M_USUAL_PRICE@AVG;
    totalRevenue = totalRevenue  +  thisRevenue;     
}

return totalRevenue/total;

Next Steps

List siblings

Sv translation

language	ja

データセットに単位価格と売れた数量のみがあり、計算された利益がない場合があります。このチュートリアルでは、利益と総売上に対しての利益率のの計算方法を教えます。

Note
このチュートリアルに進む前に、memberSum機能を十分参照することをお勧めします。

構成する表は製品グループと製品名、2つのドリルダウンレベルを持ちます。以下がその表です。

ilupng
revenue

Note
単位価格が正しく表示されていることをご確認ください。メンバー集合体はSumではなくAverageであるべきです。

利益(単位価格×量)はmembersSumを使って計算されます。

Code Block

int totalRevenue = 0;

membersSum('[L_PRODUCT_GROUP][L_PRODUCT_NAME][L_TRANSACTION_ID]') {
    int thisRevenue = M_QUANTITY * M_USUAL_PRICE@AVG;
    totalRevenue = totalRevenue + thisRevenue;     
}

return totalRevenue;

ドリルダウンレベルの順序をmembersSumのパラメーターに正しく置いたことをご確認ください。表のドリルダウンに従い、製品グループが最初、次が製品名となるはずです。最後のドリルダウンレベルである取引IDは確実に、同製品グループと同製品の全ての取引の総計をします。

利益率を計算するには、合計利益を先に計算しなければいけません。

Code Block

aggregatePrevLevel(2){ 
int totalRevenue = 0;

membersSum('[L_PRODUCT_GROUP][L_PRODUCT_NAME][L_TRANSACTION_ID]') {
    int thisRevenue = M_QUANTITY * M_USUAL_PRICE@AVG;
    totalRevenue = totalRevenue  +  thisRevenue;     
}

return totalRevenue;
}

利益率の計算コードは次の通りです:

Code Block

int total = aggregatePrevLevel(2){ 

int totalRevenue = 0;

membersSum('[L_PRODUCT_GROUP][L_PRODUCT_NAME][L_TRANSACTION_ID]') {
	int thisRevenue = M_QUANTITY * M_USUAL_PRICE@AVG;
    totalRevenue = totalRevenue  +  thisRevenue;     
}

return totalRevenue;
}

int totalRevenue = 0;

membersSum('[L_PRODUCT_GROUP][L_PRODUCT_NAME][L_TRANSACTION_ID]') {
	int thisRevenue = M_QUANTITY * M_USUAL_PRICE@AVG;
    totalRevenue = totalRevenue  +  thisRevenue;     
}

return totalRevenue/total;

次に

List siblings

Page tree

Versions Compared

Old Version 5

New Version 6

Key

Next Steps

次に