Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
doloresm
New 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 (3)
2 Solutions

Accepted Solutions
Partner
Partner

Re: Cummulative Bar Chart Accumulating/Sorting Incorrectly

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

Employee
Employee

Re: Cummulative Bar Chart Accumulating/Sorting Incorrectly

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
Partner
Partner

Re: Cummulative Bar Chart Accumulating/Sorting Incorrectly

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

doloresm
New Contributor II

Re: Cummulative Bar Chart Accumulating/Sorting Incorrectly

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

 

Partner
Partner

Re: Cummulative Bar Chart Accumulating/Sorting Incorrectly

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

Re: Cummulative Bar Chart Accumulating/Sorting Incorrectly

can you try below

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

doloresm
New Contributor II

Re: Cummulative Bar Chart Accumulating/Sorting Incorrectly

Vegar,

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

Partner
Partner

Re: Cummulative Bar Chart Accumulating/Sorting Incorrectly

That's great!

Glad I could help.

- Vegar

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Employee
Employee

Re: Cummulative Bar Chart Accumulating/Sorting Incorrectly

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

doloresm
New Contributor II

Re: Cummulative Bar Chart Accumulating/Sorting Incorrectly

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