Rolling Half Years Data

[manpri7078]

Hi All,

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.

Regards,

Manish Prasad

[Anil_Babu_Samineni]

If you have Quarter Field then you can Quarter field rather the Date

[manpri7078]

I am not able to understand what you want to say. Can you please elaborate

Regards

Manish Prasad

[Anil_Babu_Samineni]

My intention is this?

Rolling 5 Months

Sum({<Month = {">=$(=AddMonths(Max(Month),-5)) <=$(=Max(Month))"}>}Sales)

Rolling 5 Quarters

Sum({<Quarter = {">=$(=AddMonths(Max(Quarter),-5)) <=$(=Max(Quarter))"}>}Sales)

[manpri7078]

Dear Anil,

Can you please show me by way of bar chart how to make expressions for say last 2 half years. If May is selected and year 2018 is selected then only two months data is available in year 2018 so half year will mean Apr + May.

If I select 2 from the slider object, then the bar chart should show last two half years. It means last half year data will be related to Apr 17 and May 17 data combined. and the second last half year will mean data from Oct 16 to Mar 17 combined.

Similarly if 3 is selected from Slider object, then the bar chart should show last three half years. It means last half year data will be related to Apr 17 and May 17 data combined. the second last half year will mean data from Oct 16 to Mar 17 combined and the third last half year will mean data from Apr 16 to Sep 16 combined

I want to make dynamic bar chart in this way depending upon the number selected from the slider object.

Hope I am clear in my query now. Can you please guide me now as how to make bar chart and what expression I have to use in accordance with the data as per attached sample excel file.

Rolling Quarters Data Bar chart is there in my QVW document. now I want same chart for rolling half year as my query above.

Regards,

Manish Prasad

[Anil_Babu_Samineni]

Let's think, If you have data set around 2015 to Till date like Jan-2015 , Feb - 2015 ..... June-2017 . Here if you select Feb - 2017 what would be the need from your expectation ?

[manpri7078]

Dear Anil,

If I am selecting Feb-2017 and if I select 4 from the slider object then my bar chart should show :

Last Bar -  Data pertaining to Oct 2016 to Feb 2017

Second Last Bar - Data pertaining to Apr 2016 to Sep 2016

Third last Bar - Data pertaining to Oct 2015 to Mar 2016

Fourth Last Bar - Data pertaining to Apr 2015 to Sep 2015

Hope I am clear in my query now. Since I am clicking on Feb 2017 the last bar will show data for 5 months only, but all three other bars will show data for corresponding 6 months data as described above.

Regards,

Manish Prasad

[sunny_talwar]

HY is a field in your database or is this just to show us a sample? I think it would be fairly simple if we have HY as a file with HYNum as another field which would look something like this

AutoNumber(Ceil(Month(TempDate)/6) & Year(TempDate), 'HalfYear') as HYNum,

AutoNumber(Month(TempDate) & Year(TempDate), 'MonthYear') as MonthNum

Then may be just this

{<HYNum = {"$(='>=' & (Max(HYNum) - vInputRolling + 1) & '<=' & Max(HYNum))"}, MonthNum = {"$(='<=' & Max(MonthNum))"}>}

[manpri7078]

Dear Sunny,

The sample file has the same field structure as is in my database. So HY_Period and HalfYear both are in my actual database files also.

Now can you guide me accordingly

Regards,

Manish Prasad

[sunny_talwar]

Did you try the directions I gave above ... those are not working?