topic Re: Rolling Half Years Data in QlikView

Rolling Half Years Data

manpri7078 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Rolling Half Years Data

Anil_Babu_Samineni — Mon, 10 Jul 2017 08:03:47 GMT

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

Re: Rolling Half Years Data

manpri7078 — Mon, 10 Jul 2017 08:15:42 GMT

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

Regards

Manish Prasad

Re: Rolling Half Years Data

Anil_Babu_Samineni — Mon, 10 Jul 2017 08:22:46 GMT

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)

Re: Rolling Half Years Data

manpri7078 — Mon, 10 Jul 2017 08:44:25 GMT

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

Re: Rolling Half Years Data

Anil_Babu_Samineni — Mon, 10 Jul 2017 08:47:13 GMT

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 ?

Re: Rolling Half Years Data

manpri7078 — Mon, 10 Jul 2017 09:15:15 GMT

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

Re: Rolling Half Years Data

sunny_talwar — Mon, 10 Jul 2017 12:04:21 GMT

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))"}>}

Re: Rolling Half Years Data

manpri7078 — Mon, 10 Jul 2017 12:11:22 GMT

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

Re: Rolling Half Years Data

sunny_talwar — Mon, 10 Jul 2017 12:12:59 GMT

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

Re: Rolling Half Years Data

manpri7078 — Tue, 11 Jul 2017 03:22:28 GMT

Dear Sunny,

I am not using any calendar script as of now for this QVW. So can you guide me through front end any solution.

I am not very familiar with Calendar script so I usually ignore it.

Regards,

Manish Prasad

Re: Rolling Half Years Data

sunny_talwar — Tue, 11 Jul 2017 08:51:35 GMT

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;

LOAD Particulars,

Item_Grouping,

Expense_Type,

Unit,

Product,

Month,

FY,

Period,

HY_Period,

Quarter,

HalfYear,

Days,

[Mon-Y],

[P&B_Qty]

FROM

(ooxml, embedded labels, table is Data);

and then try this

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

Re: Rolling Half Years Data

manpri7078 — Tue, 11 Jul 2017 10:07:15 GMT

Dear Sunny,

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.

Regards

Manish Prasad

Re: Rolling Half Years Data

sunny_talwar — Tue, 11 Jul 2017 11:11:30 GMT

Would you be able to share the newly updated application to check it out?

Re: Rolling Half Years Data

manpri7078 — Tue, 11 Jul 2017 11:42:30 GMT

Dear Sunny,

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)

I hope you now understands my problem.

Regards

Manish Prasad

Re: Rolling Half Years Data

sunny_talwar — Tue, 11 Jul 2017 12:07:29 GMT

Slight change in how you need to define HYNum and MonthNum in the script

LOAD *,

QuarterStart([Mon-Y]) as QS,'HY'&Ceil(Month([Mon-Y])/6) as HY,

AutoNumber(HalfYear, 'HalfYear') as HYNum,

AutoNumber([Mon-Y], 'MonthYear') as MonthNum;

LOAD Particulars,

Item_Grouping,

Expense_Type,

Unit,

Product,

Month,

FY,

Period,

HY_Period,

Quarter,

HalfYear,

Days,

[Mon-Y],

[P&B_Qty]

FROM

[Sample Excel File.xlsx]

(ooxml, embedded labels, table is Data)

Where Len(Trim(Particulars)) > 0;

Re: Rolling Half Years Data

manpri7078 — Tue, 11 Jul 2017 12:17:12 GMT

Dear Sunny,

Thanks for your guidance. It is now working perfectly

Regards,

Manish Prasad

Re: Rolling Half Years Data

sunny_talwar — Tue, 11 Jul 2017 12:17:54 GMT

Awesome