Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to Create chart with Rolling values

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
18 Replies
Not applicable
Author

Hi Sanjeev,

can you explain, its urgent

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

Hi Mohammed,

Please find attached with some modifications to your expressions.

Thanks,

Sajeevan

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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