Quarter wise sum of sales

[vikasshana]

Hi,

I've the below data in my excel source and I'm looking for expression to get sum of sales as per quarter.

Date	Sales
01/01/2018	100
03/01/2018	200
10/01/2018	240
01/02/2018	120
03/02/2018	300
10/02/2018	400
01/04/2018	190
03/04/2018	432
10/04/2018	132
01/01/2019	567
03/01/2019	415
10/01/2019	690

I've edited script to get the quarter and looks like below.

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

I would like to see the output as below

Quarter	Sales
Q1-2018	1360
Q2-2018	754
Q1-2019	1672

[pradosh_thakur]

hi

Please do the following

1: Using mapping load create a Quarter column in the same table/ Create master calendar to create Quarter column

In script:

If you want the output to be done in script use 

Load Quarter, sum(Sales) as TotalSales

Resident table_name

group by Quarter;

In UI:

Table

Dimension -> Quarter

Measure -> Sum(Sales)

Thanks

Pradosh

[sunny_talwar]

Try something like this

Table:
LOAD *,
	 Dual('Q' & Ceil(Month(Date)/3) & '-' & Year(Date), QuarterStart(Date)) as QuarterYear;
LOAD * INLINE [
    Date, Sales
    01/01/2018, 100
    03/01/2018, 200
    10/01/2018, 240
    01/02/2018, 120
    03/02/2018, 300
    10/02/2018, 400
    01/04/2018, 190
    03/04/2018, 432
    10/04/2018, 132
    01/01/2019, 567
    03/01/2019, 415
    10/01/2019, 690
];

and then create a chart with

Dimension

QuarterYear

Expression

Sum(Sales)

[vikasshana]

Thanks for that Sunny and it works perfectly the way I'm expecting. How can i write an expression in 'Text Object' to get quarter wise sum of sales? For example we know Q1 sales in 2018 is 1360 and i want 1360 in one text object and Q2 sales is 754 and i want 754 in another text object etc.....

[sunny_talwar · Accepted solution]

You would use set analysis to do that

Q1-2019

=Sum({<QuarterYear = {"$(=Dual('Q' & Ceil(Month(Max(Date))/3) & '-' & Year(Max(Date)), QuarterStart(Max(Date))))"}>}Sales)

Q2-2018

=Sum({<QuarterYear = {"$(=Dual('Q' & Ceil(Month(AddMonths(Max(Date), -9))/3) & '-' & Year(AddMonths(Max(Date), -9)), QuarterStart(AddMonths(Max(Date), -9))))"}>}Sales)

Q1-2018

=Sum({<QuarterYear = {"$(=Dual('Q' & Ceil(Month(AddMonths(Max(Date), -12))/3) & '-' & Year(AddMonths(Max(Date), -12)), QuarterStart(AddMonths(Max(Date), -12))))"}>}Sales)

[vikasshana]

Thanks sunny that works perfectly.!!!

[Vijesh]

Hello Friend,

Please help me on my following post.

https://community.qlik.com/t5/Qlik-Support-Discussions/Last-Cost-and-and-Current-Cost/m-p/1545784#M1...

Thanks,

V.