Quarterly Sales report for your Organization from Sales Data

[satrajit_chatterjee]

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.

😎 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