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: 
Not applicable

How to sort months across a year change?

I am creating a table that has sum(Sales) for the last 12 months by using this formula: 

Sum(if(CloseDate <= Floor(vLoadDate) AND CloseDate > (Floor(AddMonths(vLoadDate,-12)) - Floor(Day(vLoadDate))+1), Sales))

I want to use just the first letter of each month.  My question comes with sorting.  As of today, a normal sort would show Nov & Dec (Sales Month) of LAST year after the current month of October...when I want to show Nov & Dec of last year BEFORE January of this year.  What is the best way to sort this dimension (Sales Month)?  Being that this is a rolling-12 month chart, I would want all of last year’s month sorted before this year’s month.

Any thoughts?  Thanks!

1 Solution

Accepted Solutions
d_pranskus
Partner - Creator III
Partner - Creator III

Hi

Create the following dimension in you script and use it in your chart:

DUAL(LEFT(TEXT(MONTH([Sales Date])), 1), YEAR([Sales Date]) * 100 + MONTH([Sales Date])) AS [Sales Month Cont]

Cheers

View solution in original post

3 Replies
d_pranskus
Partner - Creator III
Partner - Creator III

Hi

Create the following dimension in you script and use it in your chart:

DUAL(LEFT(TEXT(MONTH([Sales Date])), 1), YEAR([Sales Date]) * 100 + MONTH([Sales Date])) AS [Sales Month Cont]

Cheers

Not applicable
Author

Thanks a buch!

Not applicable
Author

Now that I have the dates, I’m trying to create a stacked bar chart with the dimension I am stacking called  Segment.  However, I don’t have a variable name to put in the denominator…

Sum(Sales) / Sum (Total <???> Sales)

I tried my Sales_Month and Sales_Date fields, but neither worked.  The chart renders, but the bars aren’t staking to 100%.  Any suggestions?