topic Re: Add the aggregate line to a line chart with multiple lines in QlikView

Add the aggregate line to a line chart with multiple lines

Anonymous — Fri, 02 Dec 2016 09:07:59 GMT

Dear all.

I have a following data :

Category Sub category Month Price Sales

A A1 1 100 10

A A1 2 100 10

A A1 3 100 10

A A1 4 100 10

A A1 5 100 10

A A1 6 100 10

A A2 1 100 10

A A2 2 100 10

A A2 3 100 10

A A2 4 100 10

B B1 1 100 10

B B1 2 100 10

B B1 3 100 10

B B1 4 100 10

B B1 5 100 10

B B1 6 100 10

B B2 1 100 10

B B2 2 100 10

B B2 3 100 10

B B2 4 100 10

C ......................................................

I don't want to hard code the expressions, since I'd like the user to be able to select the category names and then display the results accordingly. The line chart is monthly basis.If user chose A Category, the chart will show three monthly trend lines, one is for A1 monthly price trends, one is for A2 monthly trends and the third one is aggregation line. The logic for aggregation is sumproduct(price,sales)/total sales.

Appreciate if any ideas.

Thanks!!!

Re: Add the aggregate line to a line chart with multiple lines

Gysbert_Wassenaar — Fri, 02 Dec 2016 10:03:45 GMT

What's your question?

Re: Add the aggregate line to a line chart with multiple lines

sunny_talwar — Fri, 02 Dec 2016 10:27:46 GMT

Would you be able to share how the expected output looks like (in terms of number) to help you out better here?

Re: Add the aggregate line to a line chart with multiple lines

johnw — Sat, 03 Dec 2016 01:01:29 GMT

I don't know if I've understood, but if I have, maybe remove Category from that table, and add a new table:

Category, Sub Category with Category Total, Sub Category
A, A total, A1
A, A total, A2
A, A1, A1
A, A2, A2

...

And do your chart like this:

Dimension = Month
Dimension = Sub Category with Category Total
Expression = sum(Price * Sales) / sum(Sales)

Re: Add the aggregate line to a line chart with multiple lines

Anonymous — Mon, 05 Dec 2016 02:07:15 GMT

For example, if filtering A category, the charts should display three lines.

One for subcategory A1 monthly price trend, one for subcategory A2 monthly price trend

the third line should show aggregation . The logic for aggregation is sumproduct(price,sales)/total sales for subcategory A1 and A2.

Is it clear?

Re: Add the aggregate line to a line chart with multiple lines

Gysbert_Wassenaar — Mon, 05 Dec 2016 06:43:17 GMT

Use the solution johnw provided above.

Re: Add the aggregate line to a line chart with multiple lines

Mon, 05 Dec 2016 08:25:02 GMT

Just like John Witherspoon said,

see my sample,if could any help.