Calculate Current Quarter Sales & Previous Quarter Sales

[d_prashanthredd]

Hi All,

I have a one data set with the fields, Quarter, Product, Company and Sales.

My requirement is to achive Quarter wise total sales and also previous quarter sales against current quarter.

Input:

Year	Quarter	Sales	Product	Customer
2019	FY2019-Q1	11216.43986	A	ABC COMPANY
2019	FY2019-Q1	7408.044164	B	ABC COMPANY
2019	FY2019-Q1	23705.75342	C	ABC COMPANY
2019	FY2019-Q1	1216.98677	A	XYZ COMPANY
2019	FY2019-Q1	23705.75342	B	XYZ COMPANY
2019	FY2019-Q2	11216.43986	X	ABC COMPANY
2019	FY2019-Q2	7408.044164	Y	ABC COMPANY
2019	FY2019-Q2	23705.75342	Z	ABC COMPANY
2019	FY2019-Q2	11216.43986	X	XYZ COMPANY
2019	FY2019-Q2	23705.75342	Y	XYZ COMPANY
2019	FY2019-Q3	11094.52204	Aa	ABC COMPANY
2019	FY2019-Q3	23448.08219	Bb	ABC COMPANY
2019	FY2019-Q3	7327.521945	Cc	ABC COMPANY
2019	FY2019-Q3	11094.52204	Xx	XYZ COMPANY
2019	FY2019-Q3	7327.521945	Yv	XYZ COMPANY

Output:

Quarter	Current Qtr Sales	Previous Qtr Sales	Diff in Sales
FY2019-Q1	67252.97763	0	67252.97763
FY2019-Q2	77252.43072	67252.97763	9999.45309
FY2019-Q3	60292.17016	77252.43072	-16960.26056

Also, attached the excel file for the sample data.

[ruben_lima]

Hi,

Try this.

First of all you need to create a new table with a agregation Sales per Quarter, then try use the peek function to create the new field "Previous Qtr Sales" and create another with the diference  like this:

A:
LOAD Year,
Quarter,
Sales,
Product,
Customer
FROM
[C:\Users\rmlima\Desktop\Sample data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

B:
NoConcatenate
LOAD
*,
[Current Qtr Sales] - [Previous Qtr Sales];
load
*,
if(IsNull( Peek('Current Qtr Sales',-1)),0,Peek('Current Qtr Sales',-1)) as [Previous Qtr Sales]
;
LOAD
Quarter as QuarterB,
sum(Sales) as [Current Qtr Sales]
Resident A
Group by Quarter
Order By Quarter asc;

[d_prashanthredd]

Hi Ruben,

Thanks for your quick response.

I forgot to mention that there are many other fields in the data set and need to handle that in UI only.

Thanks,

[ruben_lima]

You can create aggregation by as many fields as you want.
In group by you can add more fields.

Is this your question?

[d_prashanthredd]

Hi Ruben,

I will try that, Is there any way to handle in front end rather than in scripting.

Thanks,

[ruben_lima]

Hi,

if i understand what you want, i think yes, you can make this aggregation in front end:

Sum(Aggr(Sum(Sales), Quarter,etc))

However I do not advise that it be done in front end. the best practice is develop in script.

Regards,