Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

bharani8
Contributor

Cumulative sum(sales)

Hi Team - I need to have the cumulative sum(sales) in descending order and their respective %. Attached is the sample data and qvw Attached.. Plz Help !

11 Replies
Highlighted
MVP
MVP

Re: Cumulative sum(sales)

Something like this?

MVP
MVP

Re: Cumulative sum(sales)

My bad, try this instead

Script

LOAD Product,

    Location,

    Sales,

    AutoNumber(Product&Location) as Key

FROM

TestData.xlsx

(ooxml, embedded labels);

Use Key as a dimension, but hide it on the presentation tab. Sort Key by Sum(Sales) in descending order to get this

Capture.PNG

bharani8
Contributor

Re: Cumulative sum(sales)

Hi Sunny - I tried above... Performance of my DB is poor.. After adding the "RangeSum(Above(TOTAL Sum(Sales), 0, RowNo(TOTAL)))" , am just stuck with the chart to get Refreshed.. It shows below Refresh icon and it is still at this screen for almost 15 mins... I have around 1M rows.. Any other better idea to improve performance?

Error.PNG

MVP
MVP

Re: Cumulative sum(sales)

The only other way I can think of is to do this in the script... but then you loose the ability to change numbers based on selection on the front end

bharani8
Contributor

Re: Cumulative sum(sales)

ohhhh!!!!

maksim_s
New Contributor

Re: Cumulative sum(sales)

Hello Sunny. I'll be really glad, if you help me with the same case.)  I have Table containing two column "date" and "sale".and I try to find out how i can get cumulative sum in each month i have. I need table like this. Please, help me =(:

  

Datesalecumulative
01.10.201710 824 05010 824 050
02.10.20178 267 37619 091 426
03.10.20178 799 37127 890 797
04.10.20178 940 36836 831 165
05.10.20179 699 60846 530 773
06.10.201710 797 77957 328 552
07.10.201712 949 54470 278 096
08.10.201710 155 18680 433 282
29.10.201710 560 744302 130 085
30.10.20178 323 429310 453 514
31.10.20178 834 970319 288 484
01.11.20178 892 9378 892 937
02.11.20179 691 61218 584 550
03.11.201712 477 17331 061 722
04.11.201714 011 62845 073 350
05.11.201711 097 01256 170 362
06.11.20179 482 52465 652 887
07.11.20177 850 06373 502 950
08.11.20178 372 51781 875 467
MVP
MVP

Re: Cumulative sum(sales)

You need this in the script or front end of the application?

maksim_s
New Contributor

Re: Cumulative sum(sales)

Will be awesome in front end of the application, if you dont mind.

MVP
MVP

Re: Cumulative sum(sales)

I have added 3 expression to show how it can be done

1) =RangeSum(Above(Sum(Sale), 0, RowNo()))

2) =Aggr(RangeSum(Above(Sum(Sale), 0, RowNo())), MonthYear, Date)

3) =Aggr(RangeSum(Above(Sum(Sale), 0, RowNo())), MonthYear, (Date, (NUMERIC)))

So, the expression 1 is dependent on your chart sorting which needs to be first by MonthYear and second by date in ascending order

expression 2 is dependent on sort in the script... for this to work, date needs to be sorted in ascending order within the script. In this case it was, so it worked.

expression 3 is only doable in QlikView 12 or above as it was introduced after with QV 12.... you can read more about it here The sortable Aggr function is finally here!‌, but the idea is that you can specify the sorting with aggr function and not just take the sorting from the load order.

HTH

Best,

Sunny