topic Re: How to sort months across a year change? in QlikView

How to sort months across a year change?

Thu, 16 Oct 2014 16:25:48 GMT

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!

Re: How to sort months across a year change?

d_pranskus — Thu, 16 Oct 2014 16:33:57 GMT

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

Re: How to sort months across a year change?

Thu, 16 Oct 2014 20:17:54 GMT

Thanks a buch!

Re: How to sort months across a year change?

Fri, 17 Oct 2014 15:16:38 GMT

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?