Qlik Community

Qlik Sense App Development

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

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Sum of last 3 months sales in Load editor

Hi everyone,

the need is :

- Get a flatten market share by summing the sales of the 2 previous months + the current one and dividing this amount by the Total sales of these 3 months.

 

Example: Total sales for Jan, Feb and Mar is 100. For a product the sales in Jan is 1, in Feb 3 and in Mar 2. The expected MS should be (1+3+2)/100 = 6% of Market Share on month March (flatten).

 

Would you get ideas about how to achieve this?

 

Thank you and regards,

Jérémie

 

 

2 Replies
Highlighted
Partner
Partner

Re: Sum of last 3 months sales in Load editor

hi,

not a nice solution, but maybe useful. For better performance you would need probable another approach (in case your data set is large)

tmpTable:
LOAD RowNo() AS ID, * Inline [
Month,Sales,ProductSales
Jan,50,1
Feb,30,3
Mar,20,2
Apr,45,3
Mai,35,4
Jun,60,5
Jul,20,6
Aug,40,7
Sep,70,8
Oct,30,5
Nov,80,4
Dec,35,6
]
;

NoConcatenate
Table:
LOAD *,
num((ProductSales_3M/TotalSales_3M),'#,0%') AS %Market_Share
;

LOAD *,
RangeSum(Sales,Previous(Sales),Previous(Previous(Sales))) as TotalSales_3M,
RangeSum(ProductSales,Previous(ProductSales),Previous(Previous(ProductSales))) as ProductSales_3M
;
LOAD ID,
Month,
Sales,
ProductSales 
Resident tmpTable
Order by ID asc
;

DROP Table tmpTable;
Highlighted
MVP
MVP

Re: Sum of last 3 months sales in Load editor

Would you be able to share sample data?