topic Re: Rolling 12 with Year List Box in QlikView

Rolling 12 with Year List Box

kpatelacmm — Mon, 17 Apr 2017 15:23:38 GMT

I want to be able to create a line graph showing sum(Sales) for a specific Year which will be in my list box. Now for 2016 and prior since those years are complete it will always pick the 12 months of that year. For 2017 however currently it will only select 4 months Jan-Apr. I need Qlikview to be able to display the rolling 12 when i select 2017. However when i pick 2016 it will display 12 months of 2016 and so forth for 2015 and prior.

I did create 12 expressions to combat this however when you do it this way there are limitations when using the line graph(you will only see data point you will not see a line). See attached file.

Epxression I am currently using created:

=Sum({<YEAR=,DateofService = {"$(=Date(AddMonths(vCheck4,-11),'YYYYMM'))"}>}Sales)

=Sum({<YEAR=,DateofService = {"$(=Date(AddMonths(vCheck4,-10),'YYYYMM'))"}>}Sales)

=Sum({<YEAR=,DateofService = {"$(=Date(AddMonths(vCheck4,-9),'YYYYMM'))"}>}Sales)

=Sum({<YEAR=,DateofService = {"$(=Date(AddMonths(vCheck4,-8),'YYYYMM'))"}>}Sales)...and so forth.

Re: Rolling 12 with Year List Box

Anonymous — Mon, 17 Apr 2017 15:27:15 GMT

Why not Sum({<YEAR=,DateofService = {"$(<=Date(AddMonths(vCheck4,-11),'YYYYMM'))"}>}Sales) ??

Re: Rolling 12 with Year List Box

kpatelacmm — Mon, 17 Apr 2017 15:30:12 GMT

Well if you see the attached file from my previous post i cannot get a line graph using the existing expression. Unless in my expression i have Sum(Sales) and in my dimension i do something that takes care of the rolling date right?

Re: Rolling 12 with Year List Box

Anonymous — Mon, 17 Apr 2017 15:34:01 GMT

You can use Date(vCheck4,'YYYYMM') as Dimension and Sum({<YEAR=,DateofService = {"<= $(=Date(AddMonths(vCheck4,-11),'YYYYMM'))"}>}Sales) as expression.

Am not completely sure which line graph you are referring to.

Re: Rolling 12 with Year List Box

kpatelacmm — Mon, 17 Apr 2017 15:37:59 GMT

I have attached the graph i am getting right now which are Dots versus a line connecting each dot.

Re: Rolling 12 with Year List Box

Anonymous — Mon, 17 Apr 2017 15:46:46 GMT

Can you try this as expression RangeSum(Above(Sum({<Month=, monthstart>} amount), 0, 12)) * Sum({<YEAR=,DateofService = {"<= $(=Date(AddMonths(vCheck4,-11),'YYYYMM'))"}>}1).

cumulative aggregation over months

Re: Rolling 12 with Year List Box

kpatelacmm — Mon, 17 Apr 2017 15:54:06 GMT

I am getting "No data to display". Dimension should be my MonthYear right?

What is the first part in the formula supposed to do? =RangeSum(Above(Sum({<MONTH=,DOS >} MM), 0, 12)) *

What to make sure i have the right fields.

Re: Rolling 12 with Year List Box

Anonymous — Mon, 17 Apr 2017 15:56:17 GMT

It will calculate the rolling sum of last 12 months data for each month

Re: Rolling 12 with Year List Box

kpatelacmm — Mon, 17 Apr 2017 17:36:53 GMT

Is there a way for the dimension to change where for 2017 it will be rolling but for other years it will the 12 months for the year selected and have the expression as Sum(Sales)?