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: 
simonsiplak
Contributor II
Contributor II

Sales of customers over years

Hi,

I am trying to get cumulative sales of my customers over years. Image  and explanation below.

So I have customers who made purchases in 2017 (for example 50 Euro). Then these same group (2017) made 70 Euro in 2018 and 150 in 2019. Customers who made their first purchase in 2018 (190 Eur) are green and in 2019 they made 190 Euro.  And so on. I am not sure what type of chart it is or how it should be called properly.

I need to calculate these numbers from invoice items table. So every row is invoice_item (Multiple rows can be one invoice)

  • price
  • generated_datetime
  • user_id
  • invoice_id
  • invoice_item_id

Can you help which chart should I use in QLIK sense and what set analysis should look like?

Screen Shot 2019-07-11 at 1.16.08 PM.png

1 Solution

Accepted Solutions
kaanerisen
Creator III
Creator III

Hi Simon,

The script should be like this;

Sales:
Load
 	*,
    year(generated_datetime) as Year;
select 
	price
	generated_datetime
	user_id
	invoice_id
	invoice_item_id
from table;

firstSalesCust:
Load
 	user_id,
    min(Year) as firstSalesYear
Resident Sales
Group By user_id;

 

After creating the data model you can use any chart you want. Pivot table or stacked bar chart as you mention seems good.

Dimenisons will be firstSalesYear and Year

Measure will be sum(Price).

Hope it helps...

View solution in original post

3 Replies
kaanerisen
Creator III
Creator III

Hi Simon,

The script should be like this;

Sales:
Load
 	*,
    year(generated_datetime) as Year;
select 
	price
	generated_datetime
	user_id
	invoice_id
	invoice_item_id
from table;

firstSalesCust:
Load
 	user_id,
    min(Year) as firstSalesYear
Resident Sales
Group By user_id;

 

After creating the data model you can use any chart you want. Pivot table or stacked bar chart as you mention seems good.

Dimenisons will be firstSalesYear and Year

Measure will be sum(Price).

Hope it helps...

simonsiplak
Contributor II
Contributor II
Author

Thanks @kaanerisen for help. It's working.  Looks really simple 🙂 

simonsiplak
Contributor II
Contributor II
Author

@kaanerisen I have one more problem with this. I need to find YoY Growth for every FirstYearSales. Can you tell me just the way how to do it? I will try to write a script.

I have following table (discussion above). I just need to find growth over the years. But problem is that simple "above" function doesn't work (because I have two dimensions Year and FirstYearSales). I tried to find something like dynamic "above" function but without success.

Screen Shot 2019-07-12 at 10.32.25 AM.png