topic Re: How accomplish Segmentation based on Business Volume in QlikView

How accomplish Segmentation based on Business Volume

lazurens — Fri, 11 May 2018 13:21:39 GMT

Hello,

I have this use case for a Qlik Sense project:

Business Volume : the total amount of orders

sum({<[Type Tier]={'CLIENT'}>}[Total Face Value])

Output :

ClientID	BV
1	800
2	3900
3	70

Segments : a label based on the business volume following these rules :

if(column(1)>=1000,'X LARGE',

if((column(1)>=250)and (column(1)<=999),'LARGE',

if((column(1)>=50)and (column(1)<=249),'MEDIUM',

if((column(1)<50),'SMALL'

))))

ClientID	BV_Segment
1	LARGE
2	XLARGE
3	MEDIUM

I have the dimension Person, which could have of two types (CLIENT, AFFILIATE) and a person can be both. The goal is to calculate the Business Volume of Clients and Create a label for each one based on the definition of segments.

The second thing is that, we need to know for a selected period the Business Volume for a Selected Client and to which Segment that Client belongs for that selected period

For example :

Client1 has a BV=300$ for the 3months(Jan,Feb,Mar of the current year 2018) and he/she in segment=Large

But the same Clien1 in different period can be in segment=XLARGE

I don't know how to nest these use cases. My intention is to use aggr(), and variables all together with Set Analysis.

I have pasted my experiments without the date, because I didn't add the Calendar to the model.

Thank you in advance.

Re: How accomplish Segmentation based on Business Volume

sunny_talwar — Fri, 11 May 2018 13:27:57 GMT

So, do you need buckets based on client and quarter?

Re: How accomplish Segmentation based on Business Volume

YoussefBelloum — Fri, 11 May 2018 13:32:32 GMT

Hi,

would you be able to show us the expected final output and attach sample data to test all this ?

Re: How accomplish Segmentation based on Business Volume

lazurens — Fri, 11 May 2018 13:45:28 GMT

Hello Sunny,

I have on my Dashboard, some filters (Year, Months, ...) so based on the selection of these filters the client expect to see the corresponding <Client, Segment> association.

Re: How accomplish Segmentation based on Business Volume

lazurens — Fri, 11 May 2018 13:48:23 GMT

Hello Youssef,

Thank you for asking the data, I myself need concrete data to experiment with also.

The data that I have is not mine but I will create an excel sheet that represent the fields and the output.

I will get back in a moment

Re: How accomplish Segmentation based on Business Volume

sunny_talwar — Fri, 11 May 2018 13:55:51 GMT

So, it should be working already... what is the exact issue you are running into?

Re: How accomplish Segmentation based on Business Volume

lazurens — Fri, 11 May 2018 14:12:50 GMT

I am attaching a manual Example, with a brainstorming visualization with Paint.

Excuse me for the poor artistic presentation of pie chart and bar charts.

Sunny, It's incomplete indeed. I still don't have this functionality working I have the If statements that represents the rule for each segment isolated from the business value calculation. I need the Segmentation to take as input the output of BV and uses that to conclude the Segment, and have all that visualized in "a" chart.

Could be Pie Chart, or Table, or Bar chart.

Thanks,

Re: How accomplish Segmentation based on Business Volume

lazurens — Fri, 11 May 2018 14:15:18 GMT

I am putting a manual Example, with a brainstorming visualization with Paint.

Excuse me for the poor artistic presentation of pie chart and bar charts.

Please follow this link

http://www.filedropper.com/manualexamplesegmentation

Re: How accomplish Segmentation based on Business Volume

sunny_talwar — Fri, 11 May 2018 14:16:22 GMT

I don't see any attachment Mohamed... did you forgot to attach it?

Re: How accomplish Segmentation based on Business Volume

lazurens — Fri, 11 May 2018 14:20:08 GMT

Yes indeed, I dropped the file but it was not there

and I can't see a button for attaching files.

Please use this link :

http://www.filedropper.com/manualexamplesegmentation

Thanks,

Re: How accomplish Segmentation based on Business Volume

sunny_talwar — Fri, 11 May 2018 14:23:09 GMT

To create pie chart.... you would need something like this

Dimension

Aggr(If(Sum({<[Type Tier] = {'CLIENT'}>} [Total Face Value]) >= 1000, 'X LARGE',

If(Sum({<[Type Tier] = {'CLIENT'}>} [Total Face Value]) >= 250, 'LARGE',

If((Sum({<[Type Tier] = {'CLIENT'}>} [Total Face Value]) >= 50, 'MEDIUM', 'SMALL'))), Client)

Expression

Count(DISTINCT Client)

Re: How accomplish Segmentation based on Business Volume

sunny_talwar — Fri, 11 May 2018 14:23:42 GMT

For help with attaching, you can look here

Uploading a Sample

Re: How accomplish Segmentation based on Business Volume

lazurens — Fri, 11 May 2018 16:10:35 GMT

Thank you Sunny,

I got your help, and I playing with now trying to get to the solution.