Problem on displaying three consecutive fiscal month on line chart.

[#1]

Hello Everyone,

I have a problem like following:

I have different list boxes containing Month, MonthOrder (Where April =1,May=2,Jun=3,..so on up to December and January=10,February=11,March=12), FinFullMonth (This is Fiscal Year like- 2006-2007,2007-2008,2008-2009,2009-2010)

In line chart I have taken Month as dimension.Now I need to show three consecutive month-suppose I have select March from list box then in line chart January,February,March these three months are displayed in dimension of line chart,to make this I used the following expression-

SUM({$<MonthOrder= {'>$(StartDate)<=$(CurrDate)'},Month=>} SAH)

Where 'SAH' is the field value which I required to show in my line chart.

Again if I select December then October,November and December would be displayed in the dimension of chart ; I have used the above expression.

But problem is :

1) when on selection of January- November and December of previous year along with January of present year need to be displayed in dimension; for example - if I select the January of 2010, then November & December month of 2009 would be displayed along with January of 2010.But in this case the previous year November and December can not be displayed with January of present year.

2) Further when I select Month April , which is starting month of fiscal year ,then only on dot is shown in my chart,but I required to show the month April along with February and March in line chart,where these February and March belongs to last fiscal year(For example- if April belongs to 2009-2010 fiscal year, then February and March belongs to fiscal year 2008-2009). For this I used the above mentioned expression :

SUM({$<MonthOrder= {'>$(StartDate)<=$(CurrDate)'},Month=>} SAH)

Where 'SAH' is the field value which I required to show in my line chart.

This is very urgent for me , please any one help me regarding this problem.

[pover · Accepted solution]

First, it might help to have just one field instead of 2. You should use the dual function as the following:

dual(test(Month),MonthOrder) as Month

then how are you calculating StartDate and CurrDate? I would use month year instead of just month.

Regards.

[#3]

Thank you Karl for your reply.

But sorry it does not work as per my requirement.

In my calculation I have used StartDate and CurrentDate as two variable, where StartDate=MonthOrder-3 and CurrentDate=MonthOrder.

In my problem MonthOrder is a listbox of numerics which specify Months as follows:

Apr=1; May=2; Jun=3; Jul=4; Aug=5; Sep=6; Oct=7; Nov=8; Dec=9; Jan=10; Feb=11; Mar=12;

The Month listbox contains Month name of above mentioned months.

Kindly explain your answer illaborately.

Thank You.

[deepakk]

hi,

Check out this Forum.

http://community.qlik.com/forums/t/41774.aspx

[#5]

Thank you for reply,

But that source did not work as per my requirement.

If you have any better example then please help me with that , this is much important for me.

Thank you.

[deepakk]

hi,

I hope the attach application helps.

[#7]

Thank you Deepak,

I have solved the problem applying the following expression :

SUM({$<Month= {$(=Month(addmonths(max(FinMonthYear),0))),$(=Month(addmonths(max(FinMonthYear),-1))),$(=Month(addmonths(max(FinMonthYear),-2)))}>} SAH)

where Month is th field containing months ; FinMonthYear contains financial months ; SAH is the field on which the application needed to be done.

Applying max function maximum FinMonthYear is selected ; then Addmonth()-function helps getting the required month as per user need ; then by application of Month() function the required month get selected.

[#8]

Thank you Mr. Karl Pover,

I have solved the problem applying the following expression :

SUM({$<Month= {$(=Month(addmonths(max(FinMonthYear),0))),$(=Month(addmonths(max(FinMonthYear),-1))),$(=Month(addmonths(max(FinMonthYear),-2)))}>} SAH)

where Month is th field containing months ; FinMonthYear contains financial months ; SAH is the field on which the application needed to be done.

Applying max function maximum FinMonthYear is selected ; then Addmonth()-function helps getting the required month as per user need ; then by application of Month() function the required month get selected.