Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
gshockxc
Contributor III
Contributor III

Grouped bar aggregation by month & year

I have been away from Qlik for a few years and my skills have gotten really rusty.  I am trying to create a grouped bar chart that shows the sums by month and compares the current year and last year.

It should like this from Excel:

gshockxc_0-1654904344330.png

However, this is the best I can do in Qlik. 

gshockxc_1-1654904391673.png

My Expression is below: Aggr(sum ( Amount/1000), Year, Month)

What am I doing wrong?

Thank you in advance for your help.

Labels (1)
1 Solution

Accepted Solutions
gshockxc
Contributor III
Contributor III
Author

After taking a few days away from this, I was able to see things more clearly.  Here's the result, and my expression.

gshockxc_0-1655124990283.png

If(Year = Year(vToday)-1, Aggr(sum ( Amount/1000), Year, Month), If(Year = Year(vToday), Aggr(sum ( Amount/1000), Year, Month)))

View solution in original post

4 Replies
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @gshockxc 

You have over complicated the expression, as the Aggr is not required, as the dimensions are shown on the chart. You can also remove the /1000 if you set the number format to Auto as that will then automatically put K on the legend if a number is over a certain amount (but show to the dollar if a single small transaction is selected).

Change the expression to just be sum(Amount) and then change the order of the dimensions under the Data properties. Do this simply by clicking and holding on Month and drag it above Year (or vice versa).

Hope that helps,

Steve

gshockxc
Contributor III
Contributor III
Author

Thanks for the quick reply.  I agree that your suggestions should work, and I've done similar in Qlikview (as I recall), but it's not working the same in Qliksense, or I'm doing something wrong.  I tried your suggestions to change the number formatting to Auto, and I'm not really happy with the result.  The Y-axis values go higher than necessary for the range of data that I'm looking at.  Is there a way to change the formatting so that I can get something like $2M, $4M, etc?

I modified the Measure to be sum(Amount)

Here's the chart result, note that I have limited the range of values to 4, and the dimensions are shown below.  Otherwise the chart displays all past years, which have '$0' values.

gshockxc_0-1654949685106.png gshockxc_1-1654949698832.png

 

I have expanded the number of values to 20, and reversed Month and Year, I get this:

gshockxc_2-1654949833309.pnggshockxc_3-1654949850713.png

The Auto formatting works better here, but I'd still like to get the scale showing $2M, $4M, etc, if possible.

Thanks in advance.

 

 

 

 

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi @gshockxc 

You may be able to lose the empty years by unticking Include Zero Values under the Add OnsData Handling menu.

Another approach for this is to exclude non zero values in Set Analysis in the expression:

sum(Amount)

To show the dollar sign on the axis and have it scale appropriately takes a bit of work, but it is possible. You need to find the max value for any month and then decide how to divide the value.

=num(sum(Amount) /
if(max(aggr(sum(Amount), Month, Year)) < 1000, 1,
if(max(aggr(sum(Amount), Month, Year)) < 1000000, 1000,
1000000))
, '$#,##0' &
if(max(aggr(sum(Amount), Month, Year)) < 1000, '',
if(max(aggr(sum(Amount), Month, Year)) < 1000000, '.0 $K',
'.0 $M')))

You would then need to leave the number format to Auto for the denominator to show.

Regarding the scale on the Y axis, it is best to leave this to auto size. You can make the gaps narrower, and make the bars reach higher to the top of the chart by selecting Narrow under Appearance and Y-Axis and Scale. Sometimes it can still end up wider than you might like and not near enough the top, but setting a manual scale (using a similar aggr statement to above) is fiddly and can give odd results.

Hope that helps.

Steve

gshockxc
Contributor III
Contributor III
Author

After taking a few days away from this, I was able to see things more clearly.  Here's the result, and my expression.

gshockxc_0-1655124990283.png

If(Year = Year(vToday)-1, Aggr(sum ( Amount/1000), Year, Month), If(Year = Year(vToday), Aggr(sum ( Amount/1000), Year, Month)))