Cumulative Sum based on Aggr()

[Keerthi1234]

Hi All,

I have a sample data as 

APM NUMBER	PRODUCT	Fiscal year period	Total cost
123	Apple	2020001	1
123	Apple	2020002	2
123	Apple	2020003	3
456	Apple	2020001	4
456	Apple	2020002	5
456	Apple	2020003	6
678	Apple	2020001	1
678	Apple	2020002	2
678	Apple	2020003	3
910	Banana	2020001	1
910	Banana	2020002	2

Here the cumulative sum must restart for each new product, but should not change even if the products' APM number is changed(the sample output is given at last). I performed the cumulative sum as 
NUM(
Aggr(
RangeSum(
Above(
Sum({1}{$}[Total cost]),
0,
RowNo(Total)
)
),
PRODUCT,[APM NUMBER]
),
'$#,##0'
)

I got the result as 

It works but it gives null values because of aggregation . I need my resulting table as 

Here I need the Product Specific cumulative Sum as required

APM NUMBER	PRODUCT	Fiscal year period	Total cost	Product Specific sum
123	Apple	2020001	1	$1
123	Apple	2020002	2	$3
123	Apple	2020003	3	$6
456	Apple	2020001	4	$10
456	Apple	2020002	5	$15
456	Apple	2020003	6	$21
678	Apple	2020001	1	$27
678	Apple	2020002	2	$29
678	Apple	2020003	3	$32
910	Banana	2020001	1	$1
910	Banana	2020002	2	$3

Has someone face this issue, Please help me understanding this.

Regards,

Keer

[ggijben]

Hi Keer, 

I did some tests with the dataset you provided. I think you have to manually create the right "RowNo scope" to include in the rangesum formula. This is wat I did:

Create a new field in your script in the table with data you provided:

AutoNumber(RowNo(), PRODUCT) AS Nr

Then use the following formula in your front end table:

RangeSum(Above(Total Sum([Total cost]),0,Nr))

This will give the following result:

I have attached my test app so you can see how I did this.

[Keerthi1234]

Hi @ggijben !

Thank you for your response, This works as expected! 🙂
In addition I have a query can't the same be performed as a single fx expression while we are creating a measure in table?

[marcus_sommer]

Usually it should be possible within the UI, too. There are dependencies to the data-model and the object-complexity and it may sometimes quite difficult if several nested logic are needed.

Before - just try to extend the above() with an own TOTAL and/or the aggr() with a NODISTINCT.

[Keerthi1234]

Thanks for the response :)! It isn't working, may be because of the dependencies as you have mentioned!

[marcus_sommer]

I suggest you try it again - within smaller steps and not as entire expression which especially means not to start with the aggr() else only the rangesum(above(sum(Field), 0, rowno())) and then extending the logic step by step with the various TOTAL  and so on.

Another approach - which I hadn't used yet - might be to look if it's solvable with:

Chart Level Scripting - Use Cases, Samples and Exa... - Qlik Community - 1981049 

[Keerthi1234]

Sure @marcus_sommer ,let me try it ,Thanks for the response :)!