Calculate Highest Revenue drawing product form a company Sales Transection Data

[satrajit_chatterjee]

Requirement

This is a very common requirement to found most revenue drawing material of a company. How to get this from a company Sales Transaction Data.

To Get Free HANA Clout system please have a look in this Video

Free SAP HANA Server Access in Cloud - YouTube

Data Files :

Please consider the following data file as a transaction information of a company.

	MAT_ID	MAT_DESC	Sale_Date	unit_price	Qty
1	MAT0001	Paper Tape 62mm x 30.48m	08/22/2015	200	3
2	MAT0002	DCPJ125/J315w- Printer	04/22/2015	3200	4
3	MAT0002	DCPJ125/J315w- Printer	05/22/2015	3200	5
4	MAT0004	Laptop - PK-100	01/22/2015	36000	6
5	MAT0005	TN2320 | 2600 str - Mouse	07/14/2015	320	2
6	MAT0006	STARCOM Dawid - Keyboard	08/14/2015	560	7
7	MAT0002	DCPJ125/J315w- Printer	08/14/2016	3200	8
8	MAT0003	12mm BLACK ON WHITE	09/14/2015	30	23
9	MAT0003	12mm BLACK ON WHITE	10/21/2015	30	12
10	MAT0005	TN2320 | 2600 str - Mouse	11/22/2015	320	1
11	MAT0002	DCPJ125/J315w- Printer	12/22/2015	3200	5
12	MAT0006	STARCOM Dawid - Keyboard	01/22/2016	560	7
13	MAT0002	DCPJ125/J315w- Printer	02/22/2016	3200	8
14	MAT0004	Laptop - PK-100	03/22/2016	36000	32
15	MAT0003	12mm BLACK ON WHITE	04/22/2016	30	2
16	MAT0002	DCPJ125/J315w- Printer	05/22/2016	3200	4
17	MAT0003	12mm BLACK ON WHITE	06/22/2016	30	11
18	MAT0005	TN2320 | 2600 str - Mouse	08/21/2016	320	3
19	MAT0006	STARCOM Dawid - Keyboard	07/21/2016	560	44
20	MAT0003	12mm BLACK ON WHITE	08/22/2016	30	52
21	MAT0004	Laptop - PK-100	08/22/2015	36000	1
22	MAT0006	STARCOM Dawid - Keyboard	04/12/2015	560	6
23	MAT0005	TN2320 | 2600 str - Mouse	05/22/2015	320	7
24	MAT0005	TN2320 | 2600 str - Mouse	06/22/2015	320	9
25	MAT0006	STARCOM Dawid - Keyboard	07/22/2015	560	9
26	MAT0002	DCPJ125/J315w- Printer	08/22/2015	3200	5
27	MAT0004	Laptop - PK-100	08/22/2016	36000	3
28	MAT0002	DCPJ125/J315w- Printer	09/30/2015	3200	20
29	MAT0003	12mm BLACK ON WHITE	10/31/2015	30	21
30	MAT0003	12mm BLACK ON WHITE	11/22/2015	30	7
31	MAT0001	Paper Tape 62mm x 30.48m	08/22/2015	200	3
32	MAT0002	DCPJ125/J315w- Printer	04/22/2015	3200	4
33	MAT0002	DCPJ125/J315w- Printer	05/22/2015	3200	5
34	MAT0004	Laptop - PK-100	06/22/2015	36000	6
35	MAT0005	TN2320 | 2600 str - Mouse	07/22/2015	320	2
36	MAT0006	STARCOM Dawid - Keyboard	08/22/2015	560	7
37	MAT0002	DCPJ125/J315w- Printer	08/22/2016	3200	8
38	MAT0003	12mm BLACK ON WHITE	09/30/2015	30	23
39	MAT0003	12mm BLACK ON WHITE	10/31/2015	30	12
40	MAT0005	TN2320 | 2600 str - Mouse	11/22/2015	320	1
41	MAT0002	DCPJ125/J315w- Printer	12/22/2015	3200	5
42	MAT0006	STARCOM Dawid - Keyboard	01/22/2016	560	7
43	MAT0002	DCPJ125/J315w- Printer	02/22/2016	3200	8
44	MAT0004	Laptop - PK-100	03/22/2016	36000	32
45	MAT0003	12mm BLACK ON WHITE	04/22/2016	30	2
46	MAT0002	DCPJ125/J315w- Printer	05/22/2016	3200	4
47	MAT0003	12mm BLACK ON WHITE	06/22/2016	30	11
48	MAT0005	TN2320 | 2600 str - Mouse	08/21/2016	320	3
49	MAT0002	DCPJ125/J315w- Printer	05/22/2015	3200	5
50	MAT0004	Laptop - PK-100	06/22/2015	36000	6

Target Report

Highest Revenue drawing product of the organization.

MAT_ID	MAT_DESC	Revenue
MAT0004	Laptop - PK-100	3096000
MAT0002	DCPJ125/J315w- Printer	313600
MAT0006	STARCOM Dawid - Keyboard	48720
MAT0005	TN2320 | 2600 str - Mouse	8960
MAT0003	12mm BLACK ON WHITE	5280
MAT0001	Paper Tape 62mm x 30.48m	1200
	`

Solution         

1) Create a Dimension Calculation View

2) Take a Projection and consume the Table in Projection

3) Take Material & Description Quantity and unit price (Take all those column for which you need get )

4) Create a calculated column, to calculate total sales value for all transaction.

5) Add an Aggregation node.

6) Add Value as aggregated column

7) add a Rank Node. Calculate Rank on Value field ascending order

😎 Activate the model and run for the output.

Please let me know if you are facing any issue.