Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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
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.
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.
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.
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
I tested your solution and ... It works; Thanks a lot !
The fact is now that I have to understand why and how