Monthly Trend

[#1]

I am using the InMonths variable to get the monthly trend for 12 months. The problem I have is that our fiscal months (5-4-4) are different than calendar months in Qlikview. Example, our October starts Sept 19 and end Oct 23. How do I go about writing a script using my fiscal months instead of calendar months. This is the script I am using today for one specific month.

sum(if (InMonths (1,TranDate, Today(), -1), if(SalesType = 'G', Sales)))

[johnw]

Generally speaking, you would create a "Fiscal Month" field in your script as part of a calendar table. So every date in the calendar would have a fiscal month specified. Rather than having separate expressions for each fiscal month in a chart, you would simply include fiscal month as a dimension. Then you would do this to get the type G sales for that fiscal month:

sum({<SalesType={'G'}>} Sales)

[#3]

I have created a Fiscal Month as part of the calendar table. What I want it do it is to show each of the last 12 months based on the last closed month. So when Nov closes, it would show Nov 2010 - Dec 2009, but it would not show Nov until it closes. I can do this using InMonths variable, but again it is for the calendar month not our fiscal month. No date range is selected in the application to achieve this. It looks something like this using InMonths. Thanks.

Office	Oct 2010	Sep 2010	Aug 2010	Jul 2010	Jun 2010	May 2010	Apr 2010	Mar 2010	Feb 2010	Jan 2010	Dec 2009	Nov 2009
	$955	$373	$740	$556	$940	$733	$1052	$670	$4065	$1755	$0	$1197
6004	$955	$373	$740	$556	$940	$733	$1052	$670	$4065	$1755	$0	$1197

[johnw]

I'd still just use FiscalMonth as the dimension, and then use set analysis to restrict it to the last 12 closed FiscalMonths, though that could get a little complicated. Maybe add a ClosedFiscalMonthSequence field to the calendar? Increment each FiscalMonth, and leave null until the FiscalMonth is closed. Then you could probably do something like this with a pivot table.

dimension 1 = Office
dimension 2 = FiscalMonth
expression = sum({<SalesType={'G'},ClosedFiscalMonthSequence={">$(=max(ClosedFiscalMonthSequence)-12)"}>} Sales)

There might be a better way, of course.

[#5]

I was trying to stay away from a pivot table but that will work. Thanks for the help.

[johnw]

mpp123 wrote:I was trying to stay away from a pivot table but that will work. Thanks for the help.

Well, you have one dimension down the side, and one across the top. That's a pivot table. But if you must have a straight table, you can do it like this:

dimension = Office
expression 1 = sum({<SalesType={'G'},ClosedFiscalMonthSequence={'$(=max(ClosedFiscalMonthSequence))'} >} Sales)
expression 2 = sum({<SalesType={'G'},ClosedFiscalMonthSequence={'$(=max(ClosedFiscalMonthSequence)-1)'} >} Sales)
...
expression 12 = sum({<SalesType={'G'},ClosedFiscalMonthSequence={'$(=max(ClosedFiscalMonthSequence)-11)'}>} Sales)

[#7]

Thanks