cancel
Showing results for
Did you mean:
Highlighted
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.

 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:

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
Labels (1)
• ### sunny_talwar

1 Solution

Accepted Solutions
Highlighted
MVP

## 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
Highlighted
Master II

## Re: Quarter wise sum of sales

hi

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

Resident table_name

group by Quarter;

In UI:

Table

Dimension -> Quarter

Measure -> Sum(Sales)

Thanks

Learning never stops.
Highlighted
MVP

## Re: Quarter wise sum of sales

Try something like this

```Table:
Dual('Q' & Ceil(Month(Date)/3) & '-' & Year(Date), QuarterStart(Date)) as QuarterYear;
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)`

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

Highlighted
MVP

## 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)`
Highlighted
Contributor III

## Re: Quarter wise sum of sales

Thanks sunny that works perfectly.!!!
Highlighted
Contributor