I am attaching both sample excel file and sample qlikview document.
I want to make a rolling bar chart for half yearly data. The last bar will represent last possible half year as per the data and previous bars will represent last n half years data depending upon the requirements of n.
My year start with April every year. So HY1 means Apr to Sep and HY2 means Oct to Mar..
Please guide me in making the expression.
For Rolling Months data I am using following expression at present :
[Mon-Y]={">$(=Num(Addmonths(Max([Mon-Y]),-5))) <=$(=Max([Mon-Y]))"} - for last 5 rolling Months
and dimension I am using is Date([Mon-Y],'MMM-YY').
and for Rolling Quarters Data I am using following expression at present :
QS={'>=$(=AddMonths(Max(QS),-(5*3-1)))<=$(=Max(QS))'} - for last 5 rolling quarters
and dimension I am using is Quarter.
Now I want to make similar expression for rolling half years for dimension representing half year.
I am attaching sample excel file and also sample QVW file for this query.
Ignoring something which will make your life easier is not a good thing... I would suggest you to add these two fields to your script (even if you don't have a master calendar field)
AutoNumber(Ceil(Month(TempDate)/6) & Year(TempDate), 'HalfYear') as HYNum,
AutoNumber(Month(TempDate) & Year(TempDate), 'MonthYear') as MonthNum
May be something like this
LOAD *,
QuarterStart([Mon-Y]) as QS,'HY'&Ceil(Month([Mon-Y])/6) as HY,
AutoNumber(Ceil(Month([Mon-Y])/6) & Year([Mon-Y]), 'HalfYear') as HYNum,
AutoNumber(Month([Mon-Y]) & Year([Mon-Y]), 'MonthYear') as MonthNum;
Thanks for your guidance. There is only one small problem here. It is working perfectly except that If in my current selection's Half year only three months have gone so far, i.e. Apr, May and Jun then the bar chart is showing the following if I am selecting 3 as vInputRolling value:
Last Bar - Three months data as only those are available so far. No problem here
Second Last Bar - Last Half year's data for Oct 16 to Mar 17. No Problem here also
Third Last Bar - 1st half data of previous year, i.e. Apr 16 to Sep 16. No Problem here also
Fourth Last Bar - Data pertaining to Jan 16 to Mar 16.
Now I am selecting only 3 but four bars are showing since my last bar data is for three months only so 1st bar is showing for three more months which are before the second bar data. 4th Last Bar should not show as such.
My expectation :
1st Bar - 1st half data of previous year, i.e. Apr 16 to Sep 16
2nd Bar - 2nd half data of previous year, i.e. Oct 16 to Mar 17
Last Bar - Data from Apr 17 to Jun 17 (Here only three months data will accumulate to Half Year Data)
Hope I am clear in my query. Otherwise your solution is working perfectly with my actual database except this.
I am attaching sample qvw document modified as per your suggestion.
Here you can see that when I am selecting 2 then bottom bar chart is showing incorrectly. Top chart is for quarterly rolling period which is showing perfectly.
When I am selecting 2 then the bottom chart should show value for H2-17 and H1-18. It is also showing data for part of the half year H1-17 (From Jul-16 to Sep-16)
