Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Can you help which chart should I use in QLIK sense and what set analysis should look like?
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...
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...
Thanks @kaanerisen for help. It's working. Looks really simple 🙂
@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.