Cummulative Bar Chart Accumulating/Sorting Incorrectly

[dbmetcalf]

I am trying to show in a bar chart, an accumulative count by Month and Term Year (i.e. Fall 2019, Fall 2020) of submitted applications.

To help sort the accumulative data in the correct month order by Term (for example Fall submitted app range is from Aug - July), I created a dual field  in app data loader script as shown below. Field name: nw_SubmittedAppMonthSort

If(num(Month([Application Submit Date]))=8 and [Start Term]='Fall', Dual(Text(Month([Application Submit Date])),1),
If(num(Month([Application Submit Date]))=9 and [Start Term]='Fall', Dual(Text(Month([Application Submit Date])),2),
If(num(Month([Application Submit Date]))=10 and [Start Term]='Fall', Dual(Text(Month([Application Submit Date])),3),
If(num(Month([Application Submit Date]))=11 and [Start Term]='Fall', Dual(Text(Month([Application Submit Date])),4),
null())))))))))))) as nw_SubmittedAppMonthSort

To accumulate the count of submitted applications between Aug - July, I found this expression in Qlik Community:

=RangeSum(Above(total Count([nw_Application Submitted]),0,RowNo(total)))

The results sort the months correctly, but accumulate each row above incorrectly (adds Fall 2019 into Fall 2020).

I tried other options (including using AGGR()) and the results are in the table below (placed in table form to show expressions used and results).

The third column in table below is how I want the values to sort, accumulating by term year per month. Any Ideas?

Start Term and Year	Count([nw_Application Submitted])	Way want it to sort/accumulated	=RangeSum(Above(total Count([nw_Application Submitted]),0,RowNo(total)))	=Aggr(RangeSum(Above(Count([nw_Application Submitted]),0,RowNo())),[Start Term and Year],nw_SubmittedAppMonthSort)	=RangeSum(Count([nw_Application Submitted]),0,RowNo())
Fall 2020	1	1	1	85	2
Fall 2019	1	1	2	80	2
Fall 2019	4	5	6	79	5
Fall 2020	3	4	9	88	5
Fall 2019	7	12	16	49	8
Fall 2020	8	12	24	56	10
Fall 2019	26	38	50	75	27
Fall 2020	28	40	78	84	30
Fall 2019	42	80	120	42	43
Fall 2020	48	88	168	48	50

[Vegar · Accepted solution]

Try an expression that looks like this:

=RangeSum(Above(sum([nw_Application Submitted]) + Sum({1} 0), 0, RowNo()))

[dbmetcalf]

Thanks so much for your response Vegar. I placed your expression (changed Above(sum( to Above(count) and got a similar result to yours except it's showing all of the months within the date range instead of just through January this year.

Any suggestions?

The bar chart Groups by the Nw_SubmittedAppMonth dimension, and the Bar value is Start Term and Year. The sorting is not custom just automatic.

Could you share your file so I can compare with what I have?

[Vegar]

Sorry, I've overwritten the file I used for generating the picture. 

If you can share a sample data set containing your dimensions (Term, month, ...) and the value fields you are using then I might be able to look into it. 

[Kushal_Chawda]

can you try below

=RangeSum(Count([nw_Application Submitted]),Above(total [ExpressionName]),0,RowNo(total)))

[dbmetcalf]

Vegar,

After reviewing my data more thouroughly, your expression works perfectly! Thanks so so much! Kudos big time :).

[Vegar]

That's great!

Glad I could help.

- Vegar

[Michael_Tarallo · Accepted solution]

Hi guys

Our latest release also has this as an option now - please let me know if it worked for you:

https://www.youtube.com/watch?v=1woZ76VMhkM

[dbmetcalf]

Great! We're on June release right now so I'll keep this in mind.