topic How to create averge line into multiple line chart in QlikView

How to create averge line into multiple line chart

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I attached my raw data and desired output in Excel File.The line chart is monthly basis. The chart will show monthly trend lines for each model, and also want to add the monthly simple average line for all models.

I also uploaded my QV file, it seemed wrong in expression. It is right for average but wrong for model trends.

Can anyone help me please.

Re: How to create averge line into multiple line chart

ramasaisaksoft — Mon, 05 Dec 2016 10:09:01 GMT

Hi Jerry,

I am unable to open your .qvw because of License issue my side.But you can view

in the Expression tab -->left hand side below you can find the Trend Lines Section.

there you can find the Average is a Check box and select that check box for your specified expression.It will show the average line chart.

Re: How to create averge line into multiple line chart

nagarjuna_kotha — Mon, 05 Dec 2016 11:02:48 GMT

May be like this

Check the attahced file.

-nagarjun

Re: How to create averge line into multiple line chart

Anonymous — Tue, 06 Dec 2016 02:10:10 GMT

Hi Nagarjun,

Thanks for the quick response.

But I want to automated update for the line charts.

That is to say if I choose models in the filter table, the line chart will updated at the same time.

Thanks

Re: How to create averge line into multiple line chart

Anonymous — Tue, 06 Dec 2016 02:13:40 GMT

I think the average reference line is for each model. Not for average of all models.

Anyway, thanks for your idea

Re: How to create averge line into multiple line chart

Anonymous — Tue, 06 Dec 2016 02:41:28 GMT

@Sunny T Can you kindly help me on this???

Very very appreciated

Re: How to create averge line into multiple line chart

sunny_talwar — Tue, 06 Dec 2016 02:50:20 GMT

Writing my name like this doesn't send me any notification . I luckily saw this post... I am taking a look at this and will let you know if I find a solution

Re: How to create averge line into multiple line chart

Anonymous — Tue, 06 Dec 2016 02:51:20 GMT

So lucky my friend.

Thanks for your kind help.

Re: How to create averge line into multiple line chart

Tue, 06 Dec 2016 02:55:05 GMT

In object properties > Presentation > Reference Lines

You can add expression and color in it.

-Ashok

Re: How to create averge line into multiple line chart

sunny_talwar — Tue, 06 Dec 2016 03:02:28 GMT

Is this what you want?

Script:

test:

LOAD Month,

Model,

[Transaction Price]

FROM

[Data test.xlsx]

(ooxml, embedded labels, table is Sheet1);

Dim:

LOAD * Inline [

Dim

];

Dimensions:

Month

=Pick(Dim, Model, 'Average')

Expression:

Pick(Dim, Sum([Transaction Price]), Avg(TOTAL <Month> {<Model>}Aggr(Sum({<Model>}[Transaction Price]), Month, Model)))

Re: How to create averge line into multiple line chart

Anonymous — Tue, 06 Dec 2016 03:07:34 GMT

Yes. The output is what I want to . But the average value seems not to be right, and one more question" Can I achieve this if I don't want to load any extra data?

Re: How to create averge line into multiple line chart

sunny_talwar — Tue, 06 Dec 2016 03:34:54 GMT

What's wrong with the average? Apr 2016 number for average is matching and so in Feb 2016 (did not check all the averages, but they should all match). The only thing different is the fact that your MonthYear is not sorted so the chart looks slightly different

There might be a very complicated way to do this without an Island Table, but I would suggest against it. This is a very simple one field Island Table which won't even impact a craziest of data models.

Re: How to create averge line into multiple line chart

Anonymous — Tue, 06 Dec 2016 03:37:26 GMT

In you example, you choose Model=A&b&c, so the average means average of ABC, not all models

Re: How to create averge line into multiple line chart

sunny_talwar — Tue, 06 Dec 2016 03:40:25 GMT

So you want to see only the average for the three models selected? Try this:

Pick(Dim, Sum([Transaction Price]), Avg(TOTAL <Month> Aggr(Sum([Transaction Price]), Month, Model)))

Re: How to create averge line into multiple line chart

Anonymous — Tue, 06 Dec 2016 03:44:53 GMT

Thanks Sunny. This is what I want. But one more question, Can I do this base on my data? I mean if I don't want to load extra dim data.

Re: How to create averge line into multiple line chart

sunny_talwar — Tue, 06 Dec 2016 03:45:56 GMT

I did respond to that part of your question above:

"There might be a very complicated way to do this without an Island Table, but I would suggest against it. This is a very simple one field Island Table which won't even impact a craziest of data models."

Re: How to create averge line into multiple line chart

sunny_talwar — Tue, 06 Dec 2016 03:47:34 GMT

If you really want to do this without Island Table, may be mrkachhiaimp‌ can share a sample which we worked on when we were playing around with this idea of not creating an Island table

Off to sleep now....

Best,

Sunny

Re: How to create averge line into multiple line chart

Anonymous — Tue, 06 Dec 2016 03:50:48 GMT

Cool. Ok. I understand.

Can I set this question more complicated?

In the above example, average is simple average. If I want to use another weighted average line to replace simple average, can it be realized?

The logic for weighted average is (sumproduct of price and sales) /sum of sales.

Sorry for occupying so much time.

Really appreciate!!!

Re: How to create averge line into multiple line chart

sunny_talwar — Tue, 06 Dec 2016 03:53:58 GMT

‌Yes sir... make it complicated, but I won't be able to get to this until tomorrow morning.

Best,

Sunny

Re: How to create averge line into multiple line chart

Anonymous — Tue, 06 Dec 2016 04:48:28 GMT

Sure. Have a nice sleep.

I will wait for you for the answer!

Thanks again.

Jerry