Reporting

[Pradyumna1]

Hi Team,

I have gross sales which I need to present it by different categories but gross sales has to be by quater.

Current:

Category	gross sales	Fiscal period
A	100	202309
B	44	202310
C	44	202308
D	212	202308
A	44	202308
B	555	202308
C	33	202309
D	211	202310
A	44	202309
B	56	202310
C	24	202309
D	67	202310
A	37	202301
B	34	202304
C	45	202305
D	66	202307
A	33	202306
B	22	202302
C	43	202303
D	456	202304
A	22	202305
B	23	202306
C	33	202306
D	22	202302

needed format :

Category	Q1(202301-04)	Q2(202305-08)	Q3(202309-12)
A	37	99	144
B	56	578	100
C	43	122	57
D	478	278	278

Please help me here

[MayilVahanan]

Hi

Try like below

LOAD *, 'Q'&Ceil(Month(Date(Date#([Fiscal period], 'YYYYMM')))/4) as Quarter INLINE [
Category, gross sales, Fiscal period
A, 100, 202309
B, 44, 202310
C, 44, 202308
D, 212, 202308
A, 44, 202308
B, 555, 202308
C, 33, 202309
D, 211, 202310
A, 44, 202309
B, 56, 202310
C, 24, 202309
D, 67, 202310
A, 37, 202301
B, 34, 202304
C, 45, 202305
D, 66, 202307
A, 33, 202306
B, 22, 202302
C, 43, 202303
D, 456, 202304
A, 22, 202305
B, 23, 202306
C, 33, 202306
D, 22, 202302
];

Am not sure, how come, you have only 3 quarters.. Suppose it should be 4, then change like below

'Q'&Ceil(Month(Date(Date#([Fiscal period], 'YYYYMM')))/3)

In front end, you Pivot table,

Dim: Category and Quarter
Exp: sum([gross sales])

[VatsalyaSolanki]

Hey @Pradyumna1 ,

Pivot Table would do your job here. 

and for calculating quarter you could use this :

'Q' & Ceil(Right([Fiscal period],2)/3) as Quarter

Refer these links for pivot table.

https://www.youtube.com/watch?v=1japJ6uj4js

https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Visualizations/PivotTabl...

[Vicky_Z]

You can either replace the value in field "Fiscal period" with Q1, Q2, Q3 or create a new calculated field, such as

if(Match([Fiscal period], '202301','202302','202303','202304'), 'Q1') & if(Match([Fiscal period], '202305','202306','202307','202308'), 'Q2') & if(Match([Fiscal period], '202309','202310','202311','202312'), 'Q3')

You can do it in Data manager or in load script. 

Then add a pivot table in the sheet. 

[MayilVahanan]

Hi

By using this method, each year, need to update the values in match function.