topic Re: How to Create chart with Rolling values in QlikView

How to Create chart with Rolling values

Wed, 19 Oct 2011 07:06:44 GMT

Hi,
I need to plot a bar chart but the data is cumulative (i.e. the data is from Jan 2007 to Current month) if i want current month values then i must subtract the current month value with the previous month value (i.e. if i want value for month of Sep 2011 then i must subtract the Sep 2011 value with the Aug 2011 Value) Similarly i have to find all the previous 12 months value and add it to get the Sep 2011 values, for your reference i have attached the Excel in that i have to create chart for Rolling value
Its Urgent can any one Help

Re: How to Create chart with Rolling values

Sokkorn — Wed, 19 Oct 2011 10:55:47 GMT

Hi,

I'm not sure my solution is the one that you looking for or not. So let check my attached file.

Regards,

Sokkorn

How to Create chart with Rolling values

Wed, 19 Oct 2011 11:09:50 GMT

Hi,
Thanks for your reply, but i am using PE version of QlikView, it would be great if you can write it in note pad or reply me

Re: How to Create chart with Rolling values

Sokkorn — Wed, 19 Oct 2011 12:41:35 GMT

Let try it:

1. Download my attached file and put it in the same directory with your QVW

2. Use this scrip

CrossTable([Jan-08], Data)
LOAD F1            AS [Header], 
     [Jan-08], 
     [Feb-08], 
     [Mar-08], 
     [Apr-08], 
     [May-08], 
     [Jun-08], 
     [Jul-08], 
     [Aug-08], 
     [Sep-08], 
     [Oct-08], 
     [Nov-08], 
     [Dec-08], 
     [Jan-09], 
     [Feb-09], 
     [Mar-09], 
     [Apr-09], 
     [May-09], 
     [Jun-09], 
     [Jul-09], 
     [Aug-09], 
     [Sep-09], 
     [Oct-09], 
     [Nov-09], 
     [Dec-09], 
     [Jan-10], 
     [Feb-10], 
     [Mar-10], 
     [Apr-10], 
     [May10], 
     [Jun-10], 
     [Jul-10], 
     [Aug-10], 
     [Sep-10], 
     [Oct-10], 
     [Nov-10], 
     [Dec-10], 
     [Jan-11], 
     [Feb-11], 
     [Mar-11], 
     [Apr-11], 
     [May-11], 
     [Jun-11], 
     [Jul-11], 
     [Aug-11], 
     [Sep-11]
FROM
[Book1.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

3. Create Bar Chart with [Jan-08] and [Header] as Dimension then use SUM(Data) as Expresion

Hope this help you.

Regards,

Sokkorn

How to Create chart with Rolling values

Wed, 19 Oct 2011 12:52:31 GMT

i think you not got wht exactly i want,

the each month Value is calculated by using formulas, i want to do it in the chart itself, See i have uploaded the cumulative data, i have to creat the chart with Rolling values wch is mut be calculated,

Re: How to Create chart with Rolling values

Anonymous — Wed, 19 Oct 2011 16:10:20 GMT

I think that there are at least two ways to accomplish this task the first is by setting a range that you want to calculate across:

RangeAvg(Above(Sum( {$<Week=, Year=, Quarter=, Month= {'>$(=WeekStart(Max(Date),-24,0))<=$(=WeekStart(Max(Date),0,0))'}>} Sales),0,12))

The above example should provide a rolling calcualtion. This example is calculating the SUM of Sales dollars in a continuously rolling 24 month range for the past 12 months.

The other way to accomplish this is to use the ACCUMULATE function on the expression tab. For the expression that you would like to provide a rolling calculation use the below formula and make sure that the number of steps you are accumulating for is = to the number of months, weeks or years that you wan tto SUM together for the rolling total.

Above(Sum(Sales)) / 12

hopefully this helps,

How to Create chart with Rolling values

Thu, 20 Oct 2011 06:28:03 GMT

Hi Ed,
Thanks alot for your reply, from your formula has given me some ideal, but i can't able to get desire values, there are to condition in my value, firstly i have to Subtract current month value with the previous month similarly have to do it for all previous 11 months then i have to sum it all to get the Actual Current Month Value,

And can u give a Brief on the fIrst formula which you have mention, how does it work

How to Create chart with Rolling values

Sat, 22 Oct 2011 09:38:14 GMT

i am still not able to get the chart,

Re: How to Create chart with Rolling values

Sun, 23 Oct 2011 11:07:13 GMT

Hi Mohammed,

You need to create some more additional date fields to achieve the results. Find below the script you need to work with

// Load the data as it is from the Excel file - I am loading only the months and the Cumulated value in to temp table

Temp:
CrossTable(Month, Value)
LOAD * FROM
C:\Book1.xlsx
(ooxml, embedded labels, table is Sheet1, filters(
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 3))
));

Data:
Load F1,
      MakeDate(Year(num#(Month)),month(num#(Month)),Day(num#(Month))) as Month,
      Year(num#(Month)) as Year,
      Value as CValue,
      if(Previous(Value)>0,Value-Previous(Value),Value) as Values resident Temp;
drop table Temp;

// Create the rolling periods which needs to be used for the graph

RollingMonths:
load Month, Month as RollingMonth, year(Month) as RollingYear resident Data;
load Month, Addmonths(Month,1,1) as RollingMonth, year(Addmonths(Month,1,1)) as RollingYear resident Data;
load Month, Addmonths(Month,2,1) as RollingMonth, year(Addmonths(Month,2,1)) as RollingYear resident Data;
load Month, Addmonths(Month,3,1) as RollingMonth, year(Addmonths(Month,3,1)) as RollingYear resident Data;
load Month, Addmonths(Month,4,1) as RollingMonth, year(Addmonths(Month,4,1)) as RollingYear resident Data;
load Month, Addmonths(Month,5,1) as RollingMonth, year(Addmonths(Month,5,1)) as RollingYear resident Data;
load Month, Addmonths(Month,6,1) as RollingMonth, year(Addmonths(Month,6,1)) as RollingYear resident Data;
load Month, Addmonths(Month,7,1) as RollingMonth, year(Addmonths(Month,7,1)) as RollingYear resident Data;
load Month, Addmonths(Month,8,1) as RollingMonth, year(Addmonths(Month,8,1)) as RollingYear resident Data;
load Month, Addmonths(Month,9,1) as RollingMonth, year(Addmonths(Month,9,1)) as RollingYear resident Data;
load Month, Addmonths(Month,10,1) as RollingMonth, year(Addmonths(Month,10,1)) as RollingYear resident Data;
load Month, Addmonths(Month,11,1) as RollingMonth, year(Addmonths(Month,11,1)) as RollingYear resident Data;

Hope this is what you were looking for.

The graph will look like below

Best Regards,

Sajeevan

How to Create chart with Rolling values

Fri, 28 Oct 2011 11:18:45 GMT

hi Sanjeev,

thanks for your reply, but am not able to get the exact chart, and me not able to understnd it, if possible can u give some more bried abt it

How to Create chart with Rolling values

Sat, 29 Oct 2011 09:22:17 GMT

Hi Sanjeev,

can you explain, its urgent

How to Create chart with Rolling values

Sun, 30 Oct 2011 07:17:40 GMT

Hi Mohammed,

Since Friday and Saturday are my weekend, I could not reply to you on time. Can you attach your QV file?

Thanks,

Sajeevan

Re: How to Create chart with Rolling values

Wed, 02 Nov 2011 16:38:55 GMT

hi Sajeevan,

sorry for the late resonse, PFA my file, as you see there are three charts in the 1st chart the cumulative value is correct but the data i have is till Sep 2011 only as u see in the charts its going till aug 2012,
In 2nd chart i have used the accumulative funcation in the expression tab, in this the chart range is till Sep 2011 only but the values are not correct,

In 3rd chart even this chart is similar to 1st chart, but in this if i select any year from the list box the values are getting changed i want the chart to display the same cumulative value if i selcet a year and if i clear all the years

any help will be really appreciated

Re: How to Create chart with Rolling values

Thu, 03 Nov 2011 07:45:54 GMT

Hi Mohammed,

Please find attached with some modifications to your expressions.

Thanks,

Sajeevan

Re: How to Create chart with Rolling values

Thu, 03 Nov 2011 08:10:35 GMT

Thanks for your reply, but i am using personal editor version of Qlikview, it would be great help if yu can write the expression in a note pad or reply

Re: How to Create chart with Rolling values

Thu, 03 Nov 2011 08:23:55 GMT

Hi Mohammed,

Use the below expression for chart 1

Sum ({<RollingMonth={"<=$(=Max(Month))"}>} Values)

Use one of the below expressions for chart 2

//Sum ({<RollingMonth={"<=$(=Max(Month))"}>} CValue)

RangeSum(Above(Sum( {$<Year=, Month= {'>$(=MonthStart(Max(Date),-12,0))<=$(=MonthStart(Max(Date),0,0))'}>} Values),0,12))

Use one of the below expressions for chart 3

//Sum({<Year=>} Values)
Sum({<Year=>} CValue)

Thanks,

Sajeevan

Re: How to Create chart with Rolling values

Fri, 18 Nov 2011 11:54:20 GMT

hi Sajeevan,

Sorry for the late response was on a short vacation, thanks for your reply its actually worked, there is one more request as you know that we are subtraction a month value with previous month and similarly adding 12 months value, but now wht client want is for January value must be not be subtracted with previous month, i tired manuplating with the condition which you have used but its not working, it would be great if you have a look in it

thanks a lot

How to Create chart with Rolling values

Sun, 20 Nov 2011 06:51:07 GMT

Hi Mohammed,

If you need to exclude Jan month from subtraction then you can use the below code.

if(Previous(Value)>0,if(Month(num#(Month))=1,Value,Value-Previous(Value)),Value) as Values resident Temp;

Hope this is what you wanted.

Regards,

Sajeevan

Re: How to Create chart with Rolling values

Mon, 21 Nov 2011 07:23:43 GMT

Hi Sajeevan,
Thanks alot for your help really appreciate it, now its worked just like a germ