Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
simonsiplak
New 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
Contributor III

Re: Sales of customers over years

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

3 Replies
kaanerisen
Contributor III

Re: Sales of customers over years

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
New Contributor II

Re: Sales of customers over years

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

simonsiplak
New Contributor II

Re: Sales of customers over years

@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