Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
dbmetcalf
Contributor II
Contributor II

Cummulative Bar Chart Accumulating/Sorting Incorrectly

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).

Capture.PNG

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 YearCount([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 2020111852
Fall 2019112802
Fall 2019456795
Fall 2020349885
Fall 201971216498
Fall 2020812245610
Fall 20192638507527
Fall 20202840788430
Fall 201942801204243
Fall 202048881684850



 

 

Labels (2)
2 Solutions

Accepted Solutions
Vegar
MVP
MVP

Try an expression that looks like this:

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

 

 

 

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Michael_Tarallo
Employee
Employee

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

Regards,
Mike Tarallo
Qlik

View solution in original post

8 Replies
Vegar
MVP
MVP

Try an expression that looks like this:

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

 

 

 

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

dbmetcalf
Contributor II
Contributor II
Author

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?

Capture2.PNG

 

Vegar
MVP
MVP

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. 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Kushal_Chawda

can you try below

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

dbmetcalf
Contributor II
Contributor II
Author

Vegar,

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

Vegar
MVP
MVP

That's great!

Glad I could help.

- Vegar

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Michael_Tarallo
Employee
Employee

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

Regards,
Mike Tarallo
Qlik

View solution in original post

dbmetcalf
Contributor II
Contributor II
Author

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