Chart with total sum and running sum

[#1]

Hi all,

I am facing a strange problem in a chart: I need to show the difference between sales and costs, by month. Now the strange problem is that sales are running sum while costs are puntual per month, so I think that I have to calculate the running sum of costs by month, but month is the dimension of my chart.

here an example of the database:

SALES

JAN 1000

FEB 2500 (1000+1500)

MAR 3400 (1000+1500+900)

....

COSTS

JAN 800

FEB 1100

MAR 600

...

This is driving me mad, thank you in advance for spending time on this, and sorry for my poor english.

Carlo A. Babini

[pablolabbe]

Carlos,

  In your database Sales are accumulated values YTD and costs are monthy values ?

The difference to be calculated is over accumulated values or monthly values ?

- Pablo Labbe

Qlikview Consultant

@Vision Gestão & Tecnologia

http://www.visiongi.com.br

[matt_crowther]

In your load script when looking at the Sales data na dassuming its in calendar order try the following:

Sales:

Load Month,

     Sales as Sales_Cummulative,

     Sales-previous(Sales) as Sales_This_Month

From dbo.Sales;

That should give you a month by month Sales value as well as the running cumulative total thus enabling a simple Sales_This_Month - Costs expression in your chart.

Of course you can always use the previous() function in a Straight Table to achieve the same effect.

Hope that helps,

Matt - Visual Analytics Ltd

@QlikviewBI

[#4]

Pablo,

yes, In my database Sales are accumulated values YTD and costs are monthy values, and the difference to be calculated is over accumulated values.

Kind regards,

Carlo

[pablolabbe · Accepted solution]

Carlo,

  Then you  need to accumulate costs. The best is to do this calculation at script level. Using the solution posed by Matthew, but  accumulating Costs.

   You ca use the peek and rangesum command to do this.  Check this post http://community.qlik.com/message/110458 where Miguel Angel Baeyens explains how to do this.

-Pablo Labbe

[#6]

Yes, this will help me a lot.

Thank you very much, you really helped me.

Kind regards

Carlo A.