Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I want to categorize a set of clients based on the spending amount.
The idea is to calculate the quartiles of the spent sum of all clients and then put each client ia a category.
For example:
I calculate first each quartile in a table :
Segments:
Load
fractile(Money,0.25) as Quartile_25,
fractile(Money,0.50) as Quartile_50,
fractile(Money,0.75) as Quartile_75
from Clientes
Money | |
Quartile 25% (Q25) | $100.00 |
Quartile 50% (Q50) | $300.00 |
Quartile 75% (Q75) | $600.00 |
And then i need, for each client, to calculate which segment he is in based in the money field calculated in the table i created before (Segments):
Segment | |
1 | Spent < Q25 |
2 | Spent > Q25 and Spent < Q50 |
3 | Spent > Q50 and Spent < Q75 |
4 | Spent > Q75 |
How can I do this? Must I use variables? how can i compare a certain field with a field of another table?
Thanks in advance
Two tables;
Intervals:
Segment, lower_value,higher_value
Spend:
Client,amount
Left join (spend)
Intervalmatch (amount)
Load
Lower_value,
Higher_value
Resident
Intervals;
Left join (client)
Load * resident intervals;
I'm on mobile now so Its hard to format the text but I hope you get the point. Help.qlik.com has some good examples on how intervalmatch works
Intervalmatch()
Thanks for your answer, and sorry to ask: how can I use intervalmatch in this case?
Two tables;
Intervals:
Segment, lower_value,higher_value
Spend:
Client,amount
Left join (spend)
Intervalmatch (amount)
Load
Lower_value,
Higher_value
Resident
Intervals;
Left join (client)
Load * resident intervals;
I'm on mobile now so Its hard to format the text but I hope you get the point. Help.qlik.com has some good examples on how intervalmatch works
Thanks again BLaise!
I understand now how it is supposed to work.
Still, i feel a little silly but, i need to ask: how can I dynamically load the first table, the Intervals one?
I mean, I only have a sales table, where i have the name of the client and tha amount spent.
I need to dynamically calculate the first, second and third quartile and assign a name for each segment.
Thanks again BLaise!
I understand now how it is supposed to work.
Still, i feel a little silly but, i need to ask this: how can I dynamically load the first table, the Intervals one?
I mean, I only have a sales table, where i have the name of the client and tha amount spent.
I need to dynamically calculate the first, second and third quartile and assign a name for each segment.