Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Try an expression that looks like this:
=RangeSum(Above(sum([nw_Application Submitted]) + Sum({1} 0), 0, RowNo()))
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
Try an expression that looks like this:
=RangeSum(Above(sum([nw_Application Submitted]) + Sum({1} 0), 0, RowNo()))
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?
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.
can you try below
=RangeSum(Count([nw_Application Submitted]),Above(total [ExpressionName]),0,RowNo(total)))
Vegar,
After reviewing my data more thouroughly, your expression works perfectly! Thanks so so much! Kudos big time :).
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
Great! We're on June release right now so I'll keep this in mind.