topic Line chart with 2 dimensions ( 1 dimension with buckets) and 1 measures in App Development

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

harveenkaur — Fri, 10 Jun 2022 03:35:51 GMT

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

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

eddie_wagt — Thu, 09 Jun 2022 13:59:45 GMT

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.

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

harveenkaur — Fri, 10 Jun 2022 03:34:56 GMT

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.

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

eddie_wagt — Fri, 10 Jun 2022 08:39:54 GMT

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

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

eddie_wagt — Fri, 10 Jun 2022 08:49:42 GMT

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