topic Re: Average Trendline isn't working properly in QlikView

Average Trendline isn't working properly

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

Hello all,

I have thes following graph which is showing only one average trendline instead of two:

It has two expressions, one for custom data labels and another with the data being displayed. If I remove the labeling expression it begins to show the 2nd trendline. Any ideas how to fix this?

The expression for this data is really long, but we can summarize it by a simple SUM:

=Sum ([Customer end-price])

The label expression is:

='Customer End Price: '&Sum ([Customer end-price]) &chr(10)
& Customer &chr(10)
&'Sales Area: ' &[SALES AREA] &chr(10)
&'Case #: ' &[ST Case Number] &chr(10)

Re: Average Trendline isn't working properly

sunny_talwar — Mon, 18 Dec 2017 18:43:39 GMT

Would you be able to share a sample to show the issue?

Re: Average Trendline isn't working properly

Anonymous — Tue, 19 Dec 2017 10:04:03 GMT

Here you have an example with the two graphs

Re: Average Trendline isn't working properly

stabben23 — Tue, 19 Dec 2017 10:25:57 GMT

Try this

Re: Average Trendline isn't working properly

stabben23 — Tue, 19 Dec 2017 10:30:55 GMT

And add pop-up label here instead

Re: Average Trendline isn't working properly

Frank_Hartmann — Tue, 19 Dec 2017 11:09:12 GMT

I think you will have to rebuild the avg trendlines in order not to loose your popup informations.

1. Expression

'Customer End Price: ' &sum([Customer End Price]) &chr(10)

&'Customer: ' &[Case #] &chr(10)&

'Sales Area: ' &[Sales Area]

2. Expression (activate avg-Trendline)

Sum({<[Case Status]={'Won'}>}[Customer End Price])/count({<[Case Status]={'Won'},[Case #]={'=[Case #]>0'}>}[Customer End Price])

3. Expression (activate avg-Trendline)

Sum({<[Case Status]={'Lost'}>}[Customer End Price])/count({<[Case Status]={'Lost'},[Case #]={'=[Case #]>0'}>}[Customer End Price])

Re: Average Trendline isn't working properly

Anonymous — Tue, 19 Dec 2017 12:02:19 GMT

If I do so not all info I want to see in the pop-up label will appear and for line charts the custom labels only work if the expression is the first one

Re: Average Trendline isn't working properly

sunny_talwar — Tue, 19 Dec 2017 12:06:42 GMT

How about if you use this as your expression and disable the other expression

Dual(

'Customer End Price: ' &sum([Customer End Price]) &chr(10)

&'Customer: ' &[Case #] &chr(10)&

'Sales Area: ' &[Sales Area],

Sum([Customer End Price]))

Re: Average Trendline isn't working properly

Anonymous — Tue, 19 Dec 2017 12:52:25 GMT

That workd for the average lines, but now the X-axis is not showing the values correctly.

Re: Average Trendline isn't working properly

sunny_talwar — Tue, 19 Dec 2017 12:53:56 GMT

I don't see any value for x-axis...

Re: Average Trendline isn't working properly

Anonymous — Tue, 19 Dec 2017 13:18:08 GMT

I'm sorry! I meant Y-axis

Re: Average Trendline isn't working properly

sunny_talwar — Tue, 19 Dec 2017 13:20:02 GMT

What is wrong with the Y axis number, the two charts above have the same looking y-axis... what am I missing?

Re: Average Trendline isn't working properly

Anonymous — Tue, 19 Dec 2017 13:46:43 GMT

My original file the Y-axis is not showing the correct value:

If I got to number and fix it, then the label doesn't work:

If I go to the axis setting I can make static steps to it instead, but then I can't configure homw many decimal places it shows, which is not optimal:

Re: Average Trendline isn't working properly

sunny_talwar — Tue, 19 Dec 2017 13:52:25 GMT

If you can share a sample where it can be replicated, I can take a look at this...

Re: Average Trendline isn't working properly

Anonymous — Tue, 19 Dec 2017 14:18:19 GMT

This is my full expression:

Dual(
     text('Customer End Price): '
          &(num(if([Offer Currency]=vCurrency,
          sum([Customer end-price]),
          sum([Customer end-price]/[Rate]* if(CurrencyCheck=vCurrency,RateCheck)))/1000000
          ,'#,##0.##', '.' , ','))&
     &chr(10)
     &'Customer: ' &Customer
     &chr(10)
     &'Sales Area: ' &[Sales Area]
     &chr(10)
     &'Case #: ' &[ST Case Number]
,
     (if([Offer Currency]=vCurrency,
          sum([Customer end-price]), 
          sum([Customer end-price]/[Rate]* if(CurrencyCheck=vCurrency,RateCheck))))/1000000
)

This report checks each currency used and converts it to the currency the user wants to see.

Re: Average Trendline isn't working properly

sunny_talwar — Tue, 19 Dec 2017 14:34:15 GMT

I don't see anything going wrong with the expression itself... don't really know, but again, if you can share a sample, I am more than happy to take a look

Re: Average Trendline isn't working properly

Anonymous — Tue, 19 Dec 2017 17:13:54 GMT

This is only happening if I divide by 1 million, if I leave the raw values the axis adjust itself correctly, any idea why?

For the data I can't share much that would help, as the rate part of the expression is online driven and it's seems to me that this part of the expression that is causing an error, since I did the same thing with the sample further above and it worked properly.

Re: Average Trendline isn't working properly

sunny_talwar — Tue, 19 Dec 2017 17:34:37 GMT

Very difficult for me to know what might be the issue without having a look, may be someone else can help

Re: Average Trendline isn't working properly

Anonymous — Wed, 20 Dec 2017 10:40:22 GMT

Hello Sunny

I managed to do a clone with some mock data so you can have a look.

Can you think of a way to work this without using static values for the axes?

Re: Average Trendline isn't working properly

sunny_talwar — Wed, 20 Dec 2017 12:21:49 GMT

I have tried quite a few things, but not able to make this work... but one thing I found was the you have used this as your environment variables

SET ThousandSep=',';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='R$ #.##0,00;-R$ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec';

SET DayNames='mon;tue;wed;thu;fri;sat;sun';

I am guessing that you don't want your Thousand and Decimal Separators to be comma. Do you know what you have to use? I would suggest fixing this in the script so that you can save some trouble later. Similarly make sure Money separators look good to....

I changed the above to this

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='R$ #.##0,00;-R$ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec';

SET DayNames='mon;tue;wed;thu;fri;sat;sun';

And using this expression

Dual(

text('Customer End Price (in M')&vCurrency&'): '

&num(if([Offer Currency]=vCurrency,

sum([Customer end-price]/1000000),

sum([Customer end-price]/[Rate /EUR]* if(CurrencyCheck=vCurrency,RateCheck)/1000000))

,'#,##0.00')

&chr(10)

&'Customer: ' &Customer

&chr(10)

&'Sales Area: ' &[Sales Area]

&chr(10)

&'Case #: ' &[ST Case Number]

num(if([Offer Currency]=vCurrency,

sum([Customer end-price]/1000000),

sum([Customer end-price]/[Rate /EUR]* if(CurrencyCheck=vCurrency,RateCheck/1000000)))

,'#,##0.00')

)

I get this

I am still unable to increase the number of decimals you would want to see, but atleast this works without having to add steps on the Axis tab.