topic Re: adding an aggregation bar in a chart in QlikView

adding an aggregation bar in a chart

edwin — Fri, 15 Nov 2024 23:21:34 GMT

we recently had a requirement to show the aggregation of all results as a bar in a chart. for example if we have weekly values, we plot the sum of the values per week (week will be the dimension). then average the resulting sum and display as the first bar.

we implemented this using a bridge in the script.

does anyone know how this can be done without any additional tables in the DM? just a regular expression in the chart?

Re: adding an aggregation bar in a chart

Frank_Hartmann — Thu, 07 Jan 2021 15:09:38 GMT

Island Table with pick(dim,...) should work!

see attached. hope this helps

Re: adding an aggregation bar in a chart

QFabian — Thu, 07 Jan 2021 15:23:26 GMT

Hi @edwin , you can try this example, that just add a line reference with the average you are looking for.

Re: adding an aggregation bar in a chart

edwin — Thu, 07 Jan 2021 15:34:43 GMT

@Frank_Hartmann we do have a few variations where we add new tables to simulate the extra dimension value (Average). but thanks for the suggestion. what i am looking for is a possible solution without adding a new table.

Re: adding an aggregation bar in a chart

edwin — Thu, 07 Jan 2021 15:34:53 GMT

@QFabian thanks for the suggestion but that isnt the visual the users specified. yes we can probably convince the user to use that instead but we looked at this as a challenge which we were able to create but i was looking for a simpler solution.

Re: adding an aggregation bar in a chart

edwin — Thu, 07 Jan 2021 15:36:06 GMT

i was thinking in line of a ValueList combined with the possible weeks, but value list accepts only constants. any ideas?

Re: adding an aggregation bar in a chart

QFabian — Thu, 07 Jan 2021 16:14:02 GMT

Hi @edwin , please try this :

Script :

///your original data

LOAD * INLINE [
Week, Amount
11-10-2020, 1100
18-10-2020, 1400
25-10-2020, 850
1-11-2020, 1300
];

///new record

concatenate
LOAD * INLINE [
Week, Amount
Average, 0
];

expression : if(Week= 'Average',avg({<Week -= {'Average'} >}total Amount), Sum (Amount))

background color : if(Week= 'Average',lightred())

result :

Re: adding an aggregation bar in a chart

edwin — Thu, 07 Jan 2021 16:46:29 GMT

@QFabian thanks, we do have a few versions similar to what you have done. we are looking for a frontend solution and hoping it might be better.

Re: adding an aggregation bar in a chart

Frank_Hartmann — Thu, 07 Jan 2021 20:16:30 GMT

You can also use Dimensionality()

see attached qvw. hope this helps

Re: adding an aggregation bar in a chart

edwin — Thu, 07 Jan 2021 21:02:08 GMT

@Frank_Hartmann That was very interesting. thanks Frank. can you pls explain how using dimensionality introduced a new column (or dimension entry)? also, it looks like you cant sort it so that the aggr is the first bar - but that should not be a deal breaker

Re: adding an aggregation bar in a chart

edwin — Thu, 07 Jan 2021 21:05:31 GMT

follow on, it appears the limitation of this solution is that you can only add one bar aside from the values of the x dimension - just an observation

Re: adding an aggregation bar in a chart

edwin — Thu, 07 Jan 2021 21:27:50 GMT

i just saw where the aggr bar is (in the dimension limits tab). thanks

Re: adding an aggregation bar in a chart

Frank_Hartmann — Thu, 07 Jan 2021 21:48:22 GMT

activating this option gives you the possibility to adress the Totalvalue by using "dimensionality()=0" in the expression. And you are right. You cannot sort the Total. The Total is always on the right side...