Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

8 Replies
matt_crowther
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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.

matt_crowther
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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.

lironbaram
Partner
Partner

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

Not applicable
Author

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

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