Skip to Content

Calculate Highest Revenue drawing product form a company Sales Transection Data

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

8) Activate the model and run for the output.

Please let me know if you are facing any issue.

2 Comments
You must be Logged on to comment or reply to a post.