Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mjamesking
Contributor

Above, Before - Stacked Bar Year on Year Change

Hi,

I'm trying to chart the year on year change in value for different values of a dimension. I can do this in  Pivot table using the Before function, I can do it year on year for the overall sum of values, by removing the dimension and using the Above function. But I can't get it working in a chart with the dimension included.

Here's an example qlikview file that shows what I'm trying to do and where I'm struggling

Could anyone help?

Thanks in advance

Matt

1 Solution

Accepted Solutions
MVP
MVP

Re: Above, Before - Stacked Bar Year on Year Change

You can do this using Aggr() function to change the order of dimensions to get the right context for your above function.

=Only(Aggr(SUM(Value) - above(SUM(Value)),Colour,Year))

To make this work, Year needs to be in chronological LOAD order, if not, you can also use a the sortable aggr() dimensions in latest QV versions:

The sortable Aggr function is finally here!

2017-03-25 09_30_07-QlikView x64 - [C__Users_Stefan_Downloads_comm254737.qvw].png

4 Replies
MVP
MVP

Re: Above, Before - Stacked Bar Year on Year Change

Not quite sure, are you looking for a grouped bar chart?

2017-03-25 00_27_43-QlikView x64 - [C__Users_Stefan_Downloads_Example (8).qvw].png

mjamesking
Contributor

Re: Above, Before - Stacked Bar Year on Year Change

‌Close, but with the grouping for the legend and axis switched.

So the x axis the Year, the grouping/dimension Colour

And a stacked chart...

MVP
MVP

Re: Above, Before - Stacked Bar Year on Year Change

You can do this using Aggr() function to change the order of dimensions to get the right context for your above function.

=Only(Aggr(SUM(Value) - above(SUM(Value)),Colour,Year))

To make this work, Year needs to be in chronological LOAD order, if not, you can also use a the sortable aggr() dimensions in latest QV versions:

The sortable Aggr function is finally here!

2017-03-25 09_30_07-QlikView x64 - [C__Users_Stefan_Downloads_comm254737.qvw].png

mjamesking
Contributor

Re: Above, Before - Stacked Bar Year on Year Change

Thank you Stefan - this is great. I shall go and play around with the sortable aggr()

Much appreciated

Matt

Community Browser