Requirement


This is a very widely used organization analysis report. How to compare quarterly Sales report for your organization. You can use stranded functionality, but date conversion sometimes will be an challenge.



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

Now we need a report like as follows.

Quarter

2015 ($$)

2016 ($$)

Q1

378000

2363040

Q2

726720

29740

Q3

132740

187320

Q4

34200

Solution:

1) Create a calculation View

2) Take a projection and consume your target tables.

3) Create 2 calculative column. 1 is Year and another is Quarter.

4) Qtr you convert the string to date format and then, use Qtr function to get the quarter of the year.

This is a generic way to convert the string to Date field

Please have the Expression here.

quarter(date(rightstr(“Sale_Date”,(strlen(rightstr(“Sale_Date”, (strlen(“Sale_Date”)-instr(“Sale_Date”,’/’)))) – instr( (rightstr(“Sale_Date”, (strlen(“Sale_Date”)-instr(“Sale_Date”,’/’)))),’/’)))+’-‘+ leftstr(“Sale_Date”, instr(“Sale_Date”,’/’)-1) + ‘-‘ + leftstr(rightstr(“Sale_Date”, (strlen(“Sale_Date”) – instr(“Sale_Date”,’/’))),(instr(rightstr(“Sale_Date”, (strlen(“Sale_Date”) – instr(“Sale_Date”,’/’))),’/’)-1))))



5) Put 2 Projection and Filter with respect to Year. Here Filter with 205 & 2016.

6) Calculate the Total value depend on quantity & Unit price.

7) Then Put an Aggregation node on top of the Projection Node.

8) Add Value as aggregated field in the node ( As shown in the Picture )

9) Do a Full outer join on top of the aggregation node

10 ) Add the output in the semantics Layer.

11) Execute and check the Output

Please let me know if you encounter any issue.

Regards

Satrajit

To report this post you need to login first.

1 Comment

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

  1. VJ RJ

    Hi Satrajit,

    This is really good share. However can you brief more on the Data file which holds transaction data.
    You can share what are all the SAP tables are used to extract this data file.

    Thanks,
    Vj
    (0) 

Leave a Reply