topic Re: Accumulation with Rangesum and multiple dimensions in QlikView

Accumulation with Rangesum and multiple dimensions

simongoodman — Thu, 21 Apr 2016 14:04:06 GMT

I am trying to create an accumulation of values('Days') based on multiple dimensions(ProductId & Phase).

I have discounted the Chart Accumulation method and tried the 'rangesum(above(sum(x), expr2))' expression method but not got it working. What is the correct expression per this example?

The second issue centers on blank field dates. In the script I can create an IF with peek function but where there blanks for two or more fields etc what is the best solution? I assume it is a loop in the script. In the attached qvw example how would it work?

Many thanks in advance

Simon

Re: Accumulation with Rangesum and multiple dimensions

sunny_talwar — Thu, 21 Apr 2016 14:06:38 GMT

What is the expected output here? Can you elaborate?

Re: Accumulation with Rangesum and multiple dimensions

simongoodman — Thu, 21 Apr 2016 14:20:21 GMT

Hi Sunny

Below is an expected output for CumDays for issue 1 and ExpectedDate for issue 2.

OrderId	ProductId	Phase	Days	CumDays	ExpectedDate
AA101042016	1	1	3	3	03/03/2016
AA101042016	1	2	2	5	05/03/2016
AA101042016	1	3	4	9	10/03/2016
AA201042016	2	1	4	4	16/04/2016
AA201042016	2	2	2	6	16/04/2016
AA201042016	2	3	1	7	17/04/2016
AA201042016	2	4	3	10	17/04/2016
BB110042016	3	1	3	3	13/04/2016
BB110042016	3	2	1	4	13/04/2016
BB110042016	3	3	1	5	13/04/2016
BB110042016	3	4	1	6	15/04/2016
BB110042016	3	5	2	8	19/04/2016

Thanks

Simon

Re: Accumulation with Rangesum and multiple dimensions

sunny_talwar — Thu, 21 Apr 2016 14:24:40 GMT

Try this:

=Rangesum(Above(sum(Days), 0, RowNo()))

Re: Accumulation with Rangesum and multiple dimensions

sunny_talwar — Thu, 21 Apr 2016 14:32:41 GMT

For the ExpectedDate requirement, you can try this:

=Date(If(Len(Trim(AllCompletedDate)) > 0, AllCompletedDate, Above([Exp Label])))

But this might not look good when you make selections because it is self referencing itself.

Re: Accumulation with Rangesum and multiple dimensions

simongoodman — Thu, 21 Apr 2016 14:55:42 GMT

I now understand my error re:rangesum and dimensions. I will test this solution with an actual large dataset.

As for your ExpectedDate solution I look into this further. As you said it is self referencing.

Thanks

Simon