Above ( ) function in Line chart gives incorrect results

[swarup_malli]

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.

!

[sunny_talwar]

See if this is resolved? PFA

Best,

Sunny

[swuehl · Accepted solution]

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)

[swarup_malli]

Thank you ! again

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

[swarup_malli]

Thank you ! Swuel .

[sunny_talwar]

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)

)

[swuehl]

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

[swarup_malli]

Sunny,

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

[swarup_malli]

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)

)

[daniel_kusiak]

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