Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Highlighted
vikasshana
New Contributor III

Quarter wise sum of sales

Hi,

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

DateSales
01/01/2018100
03/01/2018200
10/01/2018240
01/02/2018120
03/02/2018300
10/02/2018400
01/04/2018190
03/04/2018432
10/04/2018132
01/01/2019567
03/01/2019415
10/01/2019690

 

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

QuarterSales
Q1-20181360
Q2-2018754
Q1-20191672
Labels (1)
1 Solution

Accepted Solutions

Re: Quarter wise sum of sales

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)
6 Replies
pradosh_thakur
Honored Contributor II

Re: Quarter wise sum of sales

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

Learning never stops.

Re: Quarter wise sum of sales

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)

image.png

vikasshana
New Contributor III

Re: Quarter wise sum of sales

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.....

Re: Quarter wise sum of sales

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
New Contributor III

Re: Quarter wise sum of sales

Thanks sunny that works perfectly.!!!
Vijesh
New Contributor

Re: Quarter wise sum of sales