Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

d_prashanthredd
Contributor III

Calculate Current Quarter Sales & Previous Quarter Sales

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:

YearQuarterSalesProductCustomer
2019FY2019-Q111216.43986AABC COMPANY
2019FY2019-Q17408.044164BABC COMPANY
2019FY2019-Q123705.75342CABC COMPANY
2019FY2019-Q11216.98677AXYZ COMPANY
2019FY2019-Q123705.75342BXYZ COMPANY
2019FY2019-Q211216.43986XABC COMPANY
2019FY2019-Q27408.044164YABC COMPANY
2019FY2019-Q223705.75342ZABC COMPANY
2019FY2019-Q211216.43986XXYZ COMPANY
2019FY2019-Q223705.75342YXYZ COMPANY
2019FY2019-Q311094.52204AaABC COMPANY
2019FY2019-Q323448.08219BbABC COMPANY
2019FY2019-Q37327.521945CcABC COMPANY
2019FY2019-Q311094.52204XxXYZ COMPANY
2019FY2019-Q37327.521945YvXYZ COMPANY

 

Output:

QuarterCurrent Qtr SalesPrevious Qtr SalesDiff in Sales
FY2019-Q167252.97763067252.97763
FY2019-Q277252.4307267252.977639999.45309
FY2019-Q360292.1701677252.43072-16960.26056

 

Also, attached the excel file for the sample data.

5 Replies
Partner
Partner

Re: Calculate Current Quarter Sales & Previous Quarter Sales

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

Re: Calculate Current Quarter Sales & Previous Quarter Sales

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,

 

Partner
Partner

Re: Calculate Current Quarter Sales & Previous Quarter Sales

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

Re: Calculate Current Quarter Sales & Previous Quarter Sales

Hi Ruben,

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

Thanks,

Partner
Partner

Re: Calculate Current Quarter Sales & Previous Quarter Sales

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,