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.

To report this post you need to login first.

2 Comments

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

Leave a Reply