Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion Board for collaboration related to QlikView App Development.

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: Running total / cummulative

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2012-08-12
04:37 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Running total / cummulative

Hi friends,

I hav a challenge,

kindly go through the excel attached which contains two sheet ,wherein one was the sample page and the other was the result page that i would like my result to be

To brief it here ,based on the cateogory the cummulative /running total should be done, for a quik review kindly find the image attached.

kindly help me out guys!!!

Thanks ahead

Regards

Anand

16,937 Views

1 Solution

Accepted Solutions

robert99

Specialist III

2012-08-21
02:44 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I used your excel sheet and loaded into QLIKVIEW

Then I see up a chart straight table (or chart pivot table)

Then I set up

**dimensions.**

Selected

Category and item

**Expressions**

sum (Amount)

SUM(TOTAL<Category>Amount)

RangeSum (above(sum(Amount),0,RowNo()))

You will need to sort in some way to get the order the same as your excel spreadsheet

I think this will work. I have done similar at work (I use sum total category) or need to do similar whihc I will use range sum etc

8 Replies

robert99

Specialist III

2012-08-12
07:39 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Awful solution I know but it kind of works for a small number of categories. (I look forward to a better solution)

if(RowNo()=1,sum(Amount),

if(RowNo()=3,sum(Amount)+(above(sum (Amount),2))+above(sum(Amount)),

if(RowNo()=2,sum(Amount)+above(sum(Amount)),

if(RowNo()=4,sum(Amount)+(above(sum (Amount),2))+above(sum(Amount))+(above(sum (Amount),3)),

if(RowNo()=5,sum(Amount)+(above(sum (Amount),2))+above(sum(Amount))+(above(sum (Amount),3))

+(above(sum (Amount),4)),

if(RowNo()=6,sum(Amount)+(above(sum (Amount),2))+above(sum(Amount))+(above(sum (Amount),3))

+(above(sum (Amount),4))+(above(sum (Amount),5)),

if(RowNo()=7,sum(Amount)+(above(sum (Amount),2))+above(sum(Amount))+(above(sum (Amount),3))

+(above(sum (Amount),4))+(above(sum (Amount),5))+(above(sum (Amount),6))

,

0)))))))

I have just used (which has worked for me so far for my requirements)

SUM(TOTAL<Category>Amount)

to avoid the above but Im sure there is a much better solution (I just have not been able to work it out yet)

10,097 Views

robert99

Specialist III

2012-08-12
08:08 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

This seems to work too (straight and pIvot tables)

RangeSum (above(sum(Amount),1,RowNo()-1))+sum(Amount))

or

RangeSum (above(sum(Amount),0,RowNo()))

10,097 Views

Not applicable

2012-08-15
02:52 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

RJ, could you please provide the solution in qlikview with the test excel attached

Kind Regards

Anand

10,097 Views

robert99

Specialist III

2012-08-19
05:07 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Have you a problem with or difficulty understanding the solution

10,097 Views

Not applicable

2012-08-20
04:12 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

RJ,

I have difficulty in understanding the solution and so requesting you to provide me the soultion in qlikview itself with the sample data I requested .. plz help

Regards

Anand

10,097 Views

robert99

Specialist III

2012-08-21
02:44 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I used your excel sheet and loaded into QLIKVIEW

Then I see up a chart straight table (or chart pivot table)

Then I set up

**dimensions.**

Selected

Category and item

**Expressions**

sum (Amount)

SUM(TOTAL<Category>Amount)

RangeSum (above(sum(Amount),0,RowNo()))

You will need to sort in some way to get the order the same as your excel spreadsheet

I think this will work. I have done similar at work (I use sum total category) or need to do similar whihc I will use range sum etc

Not applicable

2012-08-21
07:02 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Thanks a Ton RJ , It worked ,Thanks a Lot for ur help !!!

10,097 Views

Not applicable

2012-08-21
07:06 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

RJ,

could you please let me know whether is there any possibility to do the same, apart form using the rangesum function in pivot.

just wanted to know if at all if any please let me know

Kind Regards

Anand

10,097 Views