Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Chart with total sum and running sum

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

1 Solution

Accepted Solutions
Highlighted
Luminary
Luminary

Chart with total sum and running sum

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

View solution in original post

5 Replies
Highlighted
Luminary
Luminary

Chart with total sum and running sum

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

Highlighted
Luminary
Luminary

Re: Chart with total sum and running sum

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

Highlighted
Not applicable

Chart with total sum and running sum

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

Highlighted
Luminary
Luminary

Chart with total sum and running sum

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

View solution in original post

Highlighted
Not applicable

Chart with total sum and running sum

Yes, this will help me a lot.

Thank you very much, you really helped me.

Kind regards

Carlo A.