Line chart with 2 dimensions ( 1 dimension with buckets) and 1 measures

[harveenkaur]

We want to show month wise count of states in different buckets of sales. We need to show it in 4 buckets:- 

1. 0-2000

2. 2000-5000

3. 5000-10000

4. >=10000

For example:- how many states sold between 2000 to 5000 in each month, how many sold 5000-10000 in each month and so on. This needs to be represented in line chart with X- axis as MonthYear and Y-axis as trend of how each bucket changes w.r.t. to MonthYear i.e. how many states fall in each bucket in each monthyear.

Note:- The buckets are not fixed. Buckets will be given as input by the user.

Sample data:-

[eddie_wagt]

Hello @harveenkaur ,

Nice question. I did some work for you. I used https://www.extracttable.com/ for extracting your sample data. After that, the first thing you'll need is to set up the buckets in the load script. This can be done with the intervalmatch function.

// Setting up the buckets
Bucket:
LOAD * INLINE [Key, From, To
1, 0,2000
2, 2001,5000
3, 5001, 10000
4, 10001, 99999999999]
;

// Setting up the bucket description
Bucket_Dim:
LOAD * INLINE [Key, Description
1, 0-2000
2, 2000-5000
3, 5000-10000
4, >=10000
];

// Loading the source
Source:
LOAD * INLINE [State,MonthYear,Sales
State1,Apr-21,15795
State2,Apr-21,2424
State3,Apr-21,13312
State4,Apr-21,12518
State5,Apr-21,8519
State6,Apr-21,20229
State7,Apr-21,8606
State8,Apr-21,7348
State9,Apr-21,14187
State10,Apr-21,4104
State11,Apr-21,13535
State12,Apr-21,14833
State13,Apr-21,14277
State14,Apr-21,8276
State15,Apr-21,7312
State16,Apr-21,14348
State17,Apr-21,11437
State18,Apr-21,19547
State19,Apr-21,1107
State1,May-21,6858
State2,May-21,3501
State3,May-21,2369
State4,May-21,1755
State5,May-21,9840
State6,May-21,6290
State7,May-21,9203
State8,May-21,2149
State9,May-21,7301
State10,May-21,17488
State11,May-21,13015
State12,May-21,4447
State13,May-21,6935
State14,May-21,16578
State15,May-21,1612
State16,May-21,16038
State17,May-21,14577
State18,May-21,10875
State19,May-21,12537
];

// Matching the Sales with the buckettable
left join (Source)
IntervalMatch(Sales)
LOAD From as From_tmp
,	 To as To_tmp
Resident Bucket
;
// Joining the key so we can use the description as dimension
left join (Source)
LOAD From as From_tmp 
,	 To  as To_tmp
,	 Key
Resident Bucket
;
drop fields From_tmp
,			To_tmp
from Source;
;

Make a line chart with the dimensions:

1. MonthYear

2. Description

Measure: count({$}State) 

Result

Kind regards

Eddie

If this answers your question or solves your issue, be sure to mark the answer as correct by clicking 'Accept as Solution'. This will mark the post as solved and other Qlikkies will gravitate towards this post as it as a possible solution for their issue. Multiple responses can be accepted as a solution so make sure to select all that apply.

[harveenkaur]

Thank you for the answer. But there is one more requirement. The buckets are not fixed. The buckets will be given as input by the user. I am using variable to take the input.

[eddie_wagt]

Ok, overlooked that one.

You can solve this by making variables.

1. make these variables (without a definition)
vBucket1
vBucket2
vBucket3
vBucket4
2. make these variables with definition
vBucket1_Text = '1. '&'0'&'-'&$(vBucket1)
vBucket2_Text = '2. '&$(=vBucket1+1)&'-'&$(vBucket2)
vBucket3_Text = '3. '&$(=vBucket2+1)&'-'&$(vBucket3)
vBucket4_Text = '4. '&$(=vBucket3+1)&'-'&$(vBucket4)
3. make 4 Variable input objects and set it up

Don't forget to set it as Slider and Update on Drag

Make Titles as follow : ='$(vBucket1_Text)'

4. make a Line Chart object and make this configuration
Dimensions:
MonthYear

Calculated Dimension (fill this in the expression editor)
=ValueList('$(vBucket1_Text)','$(vBucket2_Text)','$(vBucket3_Text)','$(vBucket4_Text)')
Label it

Measure
=pick(match(ValueList('$(vBucket1_Text)','$(vBucket2_Text)','$(vBucket3_Text)','$(vBucket4_Text)','$(vBucket1_Text)'),'$(vBucket1_Text)','$(vBucket2_Text)','$(vBucket3_Text)','$(vBucket4_Text)')
,count({$<Sales={">=0<=$(vBucket1)"}>}State)
,count({$<Sales={">=$(vBucket1)<=$(vBucket2)"}>}State)
,count({$<Sales={">=$(vBucket2)<=$(vBucket3)"}>}State)
,count({$<Sales={">=$(vBucket3)<=$(vBucket4)"}>}State)

)

5. Result

Regards 

Eddie

[eddie_wagt]

One more thing. It's performance is bad. Maybe you could change the input box type to a user input box. Maybe that is better. But still, because the variables are being updated each time you make changes, and we are making use here of a calculated dimension with a dynamic measure... it is really bad for the user experience. Maybe there are other Qlikkies who have a better solution for this. 

If the buckets are not frequently changing you can consider a input sheet in Excel (maybe for multiple user groups) and use this Excel in the loadscript with the first solution I provided. Or you could have a look at the Vizlib extentions for user input (or other extensions).