Rolling Growth % chart by month

[#1]

Hi all

I am tryig to create a graph that shows % growth plotted on a scrolling chart as a line..

I have loaded a Month ID field that is a numerical representation of each month for the full set of data that I have in my Data Model.

Eg..    RollingMonth, MonthID

          Jan2010          , 24

          Feb2010          , 25

          Mar2010          , 26

          ..........

          ...........

          Jan2011          , 36

          Feb2011          , 37

          Mar2011          , 38

          ....... etc

So I figured this would help me in achieving this chart but I think my limited knowlege of Set analysis is letting me down..

Any Ideas?

[swuehl]

I think your MonthID should be fine for a contigouus axis (if you have a Date type field as base for rolling Month, you could also use monthstart(DateField) to create something similar (to enable crossing the year bounderies).

So what you are trying to plot? You said Growth percentage, but for what date?

I assume you just have a Value Field next to your MonthID field, with a Value for each Month and you want to see the percentage change from Month to Month?

So you could create a linechart, use your MonthID as dimension (or maybe RollingMonth, sorted by MonthID? I think that would be easier to understand for human beings), then use as expression something like:

=(Value-above(Value))/above(Value)

(Calculating the difference of current Month's value to previous Month's value, devided by previous Month's Value), or something similar.

Then you could limit the max. visible Months to 12, if you want, in presentation tab and enable scrolling.

Maybe like attached.

So, if you want something like this, I think this should be pretty straight forward to adapt.

Hope this helps,

Stefan

[#3]

Thanks @swuehl

I realised not long after posting that the real requirement was not actually highlighted. What I need to plot is a %growth marker for the month based on the same month in the year previous - May2011 vs May2010 (not the previous month eg May2011 vs Apr2011)

I thought I could use some form of expession along the lines of =(MonthID-(MonthID-12))/(MonthID-12) but getting this to work in SET is proving more difficult than I expected.

Thank you for your post though... I hadn't come across the 'ABOVE' function yet.

This will come in handy.

Cheers

[swuehl]

Yes, that's quite hard to do with a set expression, since a set expression is evaluated only once for the complete chart. So IMHO you can't use the current dimension value inside your set expression, which you probably need to use for your calculation, if MonthID is your dimension.

What you could try is e.g. using a data island MonthID field to plot against with some if()-logic or probably better an as-of flag in your script for your MonthIDs, this concept was discussed here several times, e.g. here:

http://community.qlik.com/message/82240

Hope this helps,

Stefan

[#5]

Thanks again

I have tried to use the AsOf structure and it seems to work on the face of it however when I look at the detail of the numbers they do not marry up correctly..

I think this will be a longer term project for me to come up with a solution to...

I appreciate your help.

[swuehl]

Hi,

If you could upload a sample file, I think someone is probably able to help you (It's quite hard to tell from the distance).

Regards,

Stefan

[#7]

Hi

I think I have fixed the "AsOfMonth" way of doing this... I believe I was duplicating some of the data that was being looked at.... I made a fundamental mistake of using the following expression

num(sum([In company code currency_HSL]*-1*RatetoUSD),'$###.#m')/1000000

as opposed to what I should have used to calculate the sales for each month..

num(sum({<YearType={'Current'}>}[In company code currency_HSL]*-1*RatetoUSD),'$###.#m')/1000000

This happened partly due to trying to maintain a predecessors app...

Thank you again for all your efforts to help on this.