topic Re: Help calculation between months in QlikView

Help calculation between months

Tue, 16 Aug 2011 15:34:57 GMT

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

matt_crowther — Tue, 16 Aug 2011 15:44:55 GMT

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

Tue, 16 Aug 2011 15:54:56 GMT

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

matt_crowther — Tue, 16 Aug 2011 16:07:03 GMT

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

Help calculation between months

Tue, 16 Aug 2011 17:11:16 GMT

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

Wed, 17 Aug 2011 09:52:00 GMT

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.

Re: Help calculation between months

Thu, 18 Aug 2011 11:49:51 GMT

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

lironbaram — Thu, 18 Aug 2011 12:12:44 GMT

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

Fri, 19 Aug 2011 12:17:20 GMT

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

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