topic Re: How to cummulate with selection? in QlikView

How to cummulate with selection?

Fri, 02 Dec 2011 08:26:46 GMT

Hi,

i have a question regarding cummulation of values with different dimensions. Database is a list with changes. I want to calculate the cumulative value for the different dimensions. Please see the dataset

DAY	MANDT	PORTION	CHANGE	CUMM
01.01.2009	100	ABCDE	10.000	10.000
01.01.2009	100	VWXYZ	5.500	5.500
02.01.2009	100	ABCDE	100	10.100
02.01.2009	100	VWXYZ	5	5.505
03.01.2009	100	ABCDE	-10	10.090
03.01.2009	100	VWXYZ	-3	5.502
04.01.2009	100	ABCDE	30	10.120
04.01.2009	100	VWXYZ	6	5.508
05.01.2009	100	ABCDE	-40	10.080
05.01.2009	100	VWXYZ	6	5.514
06.01.2009	100	ABCDE	20	10.100
06.01.2009	100	VWXYZ	8	5.522
07.01.2009	100	ABCDE	30	10.130
07.01.2009	100	VWXYZ	5	5.527
08.01.2009	100	ABCDE	-40	10.090
08.01.2009	100	VWXYZ	3	5.530

My question is, how can i calculate the correct cumulative value within the comlete dataset, even if i selected a specific date (07.01, 08.01), in other words calculate the correct cumulative value (without selection) for specific dimensions from start of dataset till selected date (s.th like sum ({$<date >= (date (01.01.2009, DD.MM.YYYY)>} Changes) ?? ). The result should look like this with selection of 07.01.2009 and 08.01.2009:

DAY	MANDT	PORTION	CHANGE	CUMM
07.01.2009	100	ABCDE	30	10.130
07.01.2009	100	VWXYZ	5	5.527
08.01.2009	100	ABCDE	-40	10.090
08.01.2009	100	VWXYZ	3	5.530

Thank you very much for your help!!

Greetz

How to cummulate with selection?

Mon, 05 Dec 2011 10:24:51 GMT

has no one a hint in which direction I can search? - Please

Thank you very much!

How to cummulate with selection?

Miguel_Angel_Baeyens — Mon, 05 Dec 2011 10:36:37 GMT

Hi,

Use a variable in a slider/calendar object instead of selecting in the field. Then use this variables in the set analysis as in this application.

Hope that helps.

Miguel

Re: How to cummulate with selection?

Mon, 05 Dec 2011 14:07:33 GMT

Hi Miguel,

thank you very much for your answer. Your file is very helpful.

I used the Date Variables (vStartDate and vEndDate) to select the changes. For the Sum (cumulative) i used the whole time period. The problem is that the Dimension (Days) is shown for the whole period.. For the selected time period (vStart.. till vEnd..) the changes have the value 0.

In addition the cumulative sum is not correct. Do you have an idea what the problem might be.

Thank you so much!

Jezze

Re: How to cummulate with selection?

Miguel_Angel_Baeyens — Mon, 05 Dec 2011 14:21:33 GMT

Hi Jezze,

Not sure if I got you right. Check the ammended file though.

Hope that helps.

Miguel

Re: How to cummulate with selection?

Mon, 05 Dec 2011 15:35:58 GMT

Hi Miguel,

thank you for your answer. Maybe the cumulative sum is not working for more than one dimension..

Your formula "RangeSum(Above(Sum(SalesAmount), 0, RowNo()))" is a good idea and shows the same result like using the radio button "Accumulation" in the formula register.

I´m sorry that i did not explained clearly how the final table should look like. It would be great if following requirements could be met or ideas how i can handle them.

It should be possible to limit the time table (no problem -> normal functionality)

The line "changes" should respond to the time restriction and only represent the values in the Time range (e.g. 07.01.2009 - 08.01.2009).

The row "Cumm" should not react to the time restriction and display the sum of all values from the first day (MinDate) until now or until the value shown in each casewithin the time range. (e.g. Sum from 01.01.2009 until 07.01.2009 and next line sum 01.01.2009 till 08.01.2009)

If possible, the cumulative amount should not only be one dimension (date) will becalculated correctly but after several dimensions.

I´ m really excited for your ideas.

Thank you very much for your help.

Greetings

Jezze

Re: How to cummulate with selection?

Miguel_Angel_Baeyens — Mon, 05 Dec 2011 15:59:02 GMT

Hi Jezze,

Indeed I was wrong. Is that what you are looking for?

Miguel

Re: How to cummulate with selection?

Mon, 05 Dec 2011 17:29:48 GMT

Hi Miguel,

that looks great, thank you,..

There is only one functionality missing - but i see, i didn´t explain it very well - i´m very sorry for that.

The "Cumm" columns should be calculated as a sum of all values of the "Change" columns from the first day of dataset till the selected date range.

The "Cumm" column should not be a part of the original dataset. The dataset should only consist of Day, Mand, Portion and Change. (I just wanted to show it to explain how the values are calculated).

Best greetings

Jezze