8 Replies Latest reply: Aug 19, 2011 8:17 AM by Alain NICALEK

# Help calculation between months

Hello,

I try to create a chart showing the evolution of a stock each month of a year.

Here is my table

Month   FTP

Jan     100

Feb     150

Mar     130

Apr.    160

I managed to use Set Analysis when a user selects a specific month, I can calculate the previous month value and then, the difference with selected month.

sum(FTP)-sum({\$<MonthID={\$(=Max(MonthID)-1)},Year=,Month=>}FTP)

If the user selects March it shows -20 (130-150)

But this works only for one month. If I want to display the values for twelve monthes (having Month as a dimension) it does not work. There seems to be some trouble between the set analysis using Month and the dimension of the chart.

I found a way with crosstable and before, having Month as Dimension.

Sum (FTP) - before(Sum (FTP))

This gives me the crosstable :

Jan    Feb    Mar     Apr

-      50     -20     30

But this only works with a table. Not a chart.

How could I create a chart showing a bar for each month, and the bar being the difference between this month and previous month.

Thank you for your help, because I am really stuck.

Alain

• ###### Help calculation between months

Alain,

Can you post a simple .qvw example as this sounds possible - I've done similar things in the past but for me to write the required script free-hand might be a bit much!

All the best,

Matt - Visual Analytics Ltd

• ###### Help calculation between months

I will create a small example and add it here.

Someone  pointed me to the "Above" function so I am also trying it, but it seems to work only with on dimension in the graph.

• ###### Help calculation between months

The functions above(), previous() and before() work differently (or not at all) depending on whether you're working with a Pivot Table or a Straight Table (all charts bar the Pivot Table have a Straight Table that drives them apparently).

I'll keep an eye out for the example - my email notification doesn't work so if I don't respond straight away I'm not ignoring the issue!

All the best,

Matt - Visual Analytics Ltd

• ###### Re: Help calculation between months

Hello,

The above function already helped me in creating the expected chart.

I still cannot create a piled chart (Chart with two dimensions) and I have uploaded an example with what I am trying to do and what I managed to realize.

Thank you a lot for your help.

• ###### Re: Help calculation between months

hei

attach is an example of the graph you want

i created data islands with monthyear and the previous month year

then changed a little bit the formula

hope its helps you

• ###### Re: Help calculation between months

I tested your solution and ... It works; Thanks a lot !

The fact is now that I have to understand why and how

• ###### Help calculation between months

Setup 12 set analysis expressions:

Last month to current month change:

sum({\$<MonthID={\$(=Max(MonthID))},Year=,Month=>}FTP)-sum({\$<MonthID={\$(=Max(MonthID)-1)},Year=,Month=>}FTP)

Prior months change:

sum({\$<MonthID={\$(=Max(MonthID)-1)},Year=,Month=>}FTP)-sum({\$<MonthID={\$(=Max(MonthID)-2)},Year=,Month=>}FTP)

Repeat the above expression to take you back 12 months using =(max(MonthID)), =(max(MonthID)-1), etc... as your expression label.

This will give you a rolling 12 month chart that goes backwards from whichever month the user has selected.

• ###### Re: Help calculation between months

Thank you all for your answers that gave me some ideas and a start of solution.

I achieved some results with "above" or a fixed number of set analysis but not completely as i would like the graph to be split in dimensions.

I have added an example of what I am looking for and what i have achieved already with your help.