topic Chart with total sum and running sum in QlikView

Chart with total sum and running sum

Wed, 18 Jan 2012 16:30:56 GMT

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

Chart with total sum and running sum

pablolabbe — Wed, 18 Jan 2012 16:49:52 GMT

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

Re: Chart with total sum and running sum

matt_crowther — Wed, 18 Jan 2012 16:53:29 GMT

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

Chart with total sum and running sum

Thu, 19 Jan 2012 09:05:10 GMT

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

Chart with total sum and running sum

pablolabbe — Thu, 19 Jan 2012 11:42:47 GMT

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

Chart with total sum and running sum

Thu, 19 Jan 2012 13:15:05 GMT

Yes, this will help me a lot.

Thank you very much, you really helped me.

Kind regards

Carlo A.