topic Re: Above ( ) function in Line chart gives incorrect results in QlikView

Above ( ) function in Line chart gives incorrect results

swarup_malli — Thu, 18 Jun 2015 15:08:03 GMT

Hi,

I have a straight table that usezs above () function and gives me right values.

But when the same formula is used in a line chart it gives wrong results .

The strangest part is when I select a value in year dimension it gives me right values, but I want to implement the logic in expression itself.

Re: Above ( ) function in Line chart gives incorrect results

sunny_talwar — Thu, 18 Jun 2015 15:13:47 GMT

See if this is resolved? PFA

Best,

Sunny

Re: Above ( ) function in Line chart gives incorrect results

swuehl — Thu, 18 Jun 2015 15:17:58 GMT

You are using a line chart with dimension Month and Year, so for multiple years, the underlying table looks like

Jan, 2014

Jan, 2015

Feb, 2014,

Feb, 2015

While you need it to be

2014, Jan

2014, Feb

...

2015, Jan

2015, Feb

You can change the order of dimensions by using advanced aggregation aggr() function in your expression, but this would need Month to have a chronologic load order (which I think you don't have right now).

=aggr(

YourExpression

, YearField, MonthField)

Re: Above ( ) function in Line chart gives incorrect results

swarup_malli — Thu, 18 Jun 2015 15:41:42 GMT

Thank you ! again

What exactly did you do, because the formula hasn't changed.I'm guessing yo9u must have changed some setting

Re: Above ( ) function in Line chart gives incorrect results

swarup_malli — Thu, 18 Jun 2015 15:42:18 GMT

Thank you ! Swuel .

Re: Above ( ) function in Line chart gives incorrect results

sunny_talwar — Thu, 18 Jun 2015 15:44:56 GMT

formula did change from:

(Sum ({<REPORT_YEAR={2015}>}FLIGHT_HRS)

/ COUNT({<REPORT_YEAR={2015}>} AC_SERIAL)

* COUNT({<REPORT_YEAR={2015}>} AC_SERIAL)

)

above( total(Sum ( FLIGHT_HRS)

/ COUNT( AC_SERIAL) )

* COUNT(AC_SERIAL)

)

to:

(Sum ({<REPORT_YEAR={2015}>}FLIGHT_HRS)

/ COUNT({<REPORT_YEAR={2015}>} AC_SERIAL)

* COUNT({<REPORT_YEAR={2015}>} AC_SERIAL)

)

above( total(Sum ({<REPORT_YEAR={2015}>} FLIGHT_HRS)

/ COUNT({<REPORT_YEAR={2015}>} AC_SERIAL) )

* COUNT({<REPORT_YEAR={2015}>}AC_SERIAL)

)

Re: Above ( ) function in Line chart gives incorrect results

swuehl — Thu, 18 Jun 2015 15:46:32 GMT

Then you can also remove the dimension REPORT_YEAR from the chart ...

Re: Above ( ) function in Line chart gives incorrect results

swarup_malli — Thu, 18 Jun 2015 15:47:33 GMT

Sunny,

but I lose the first month ( in this case Jan ..because of above function )

Re: Above ( ) function in Line chart gives incorrect results

swarup_malli — Thu, 18 Jun 2015 15:48:40 GMT

I want to retain Jan also ..that's why I'm not using {<REPORT_YEAR={2015}>} in the below expression

above( total(Sum ( FLIGHT_HRS)

/ COUNT( AC_SERIAL) )

* COUNT(AC_SERIAL)

)

Re: Above ( ) function in Line chart gives incorrect results

daniel_kusiak — Thu, 18 Jun 2015 17:28:51 GMT

Manual about above/below - Missing Manual - Above() and Below()

Re: Above ( ) function in Line chart gives incorrect results

swarup_malli — Thu, 18 Jun 2015 18:53:19 GMT

Thank you ,swuel ..I tried it and got the result