Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik_Bio06
Contributor III
Contributor III

display the whole year per month

Hi,

How can I show the sales (CHF) for the whole year 2020 per month Jan-Dec correctly?

What do I have to do with the date column 'Created on' so that I can see every month in the chart.

 

Thanks for the help.

Qlik_Bio06_0-1618326580820.png

 

Labels (1)
1 Solution

Accepted Solutions
Dalton_Ruer
Support
Support

You should edit your load script to contain a field called "MONTH" something like:

Load

DateField,

Month(DateField) as Date_Month,

Year(DateField) as Date_Year,

Value

.....

Then in your Bar Chart you can simply use Date_Month as the Dimension so your Sum(Value) will aggregate up to the month. 

NOTE: If you do exactly like the example your months will sort alphabetically April will be first, August will be second etc. You can solve that problem using a DUAL expression to create both the Textual name of the month and the numerical value of the month

Dual(Month(DateField), Num(Month(DateField)) as Date_Month

You can concatenate things together in the event you want the year and the month 

Year(DateField) & '-' & Month(DateField) as Date_YearMonth

You can also use a Dual data type so that 2020-January shows before 2020-April but obviously you don't want to use the same NUM(Month function like above because you don't want January 2021 to show up before February 2020. So for the numerical portion you can use MonthStart(DateField) like:

Dual(Year(DateField)&'-'&Month(DateField), MonthStart(DateField)) as [Date_YearMonth]

 

 

 

View solution in original post

1 Reply
Dalton_Ruer
Support
Support

You should edit your load script to contain a field called "MONTH" something like:

Load

DateField,

Month(DateField) as Date_Month,

Year(DateField) as Date_Year,

Value

.....

Then in your Bar Chart you can simply use Date_Month as the Dimension so your Sum(Value) will aggregate up to the month. 

NOTE: If you do exactly like the example your months will sort alphabetically April will be first, August will be second etc. You can solve that problem using a DUAL expression to create both the Textual name of the month and the numerical value of the month

Dual(Month(DateField), Num(Month(DateField)) as Date_Month

You can concatenate things together in the event you want the year and the month 

Year(DateField) & '-' & Month(DateField) as Date_YearMonth

You can also use a Dual data type so that 2020-January shows before 2020-April but obviously you don't want to use the same NUM(Month function like above because you don't want January 2021 to show up before February 2020. So for the numerical portion you can use MonthStart(DateField) like:

Dual(Year(DateField)&'-'&Month(DateField), MonthStart(DateField)) as [Date_YearMonth]