Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vikasshana
Creator II
Creator II

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
sunny_talwar

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)

View solution in original post

6 Replies
pradosh_thakur
Master II
Master II

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

image.png

vikasshana
Creator II
Creator II
Author

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

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
Creator II
Creator II
Author

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