topic Re: valueloop in chart in QlikView

valueloop in chart

sr_qlikview — Tue, 26 Feb 2013 20:49:43 GMT

hello all

i am using value loop on the dimension to get years (vMaxyr = 2012, vPrYr = 2010) , want to get sum of sales, how do we defnie expression for the corresponding year from valueloop??

Re: valueloop in chart

swuehl — Tue, 26 Feb 2013 21:43:06 GMT

If you are using you using something

=valueloop( 2010, 2012, 1)

as dimension,

the exact same will return the current dimension value when used in an expression:

e.g.

=if(valueloop( 2010, 2012, 1) = 2011, 'year in the middle', 'either start or end year')

Re: valueloop in chart

sr_qlikview — Tue, 26 Feb 2013 21:58:51 GMT

if i use the valueloop(2010,2012,1) (actually my years in variables, but still tried with hardcoded yrs), expression sum(sales) gives the same value (sum of all 3yrs) for all 3 years, and chart dimension listed 2010,2011,2012

Re: valueloop in chart

swuehl — Tue, 26 Feb 2013 22:38:43 GMT

Sure, the synthetic dimension you are creating with valueloop() is not connected to your data model, so you are getting the same value back for all dimension values if you are using something like

=sum(sales)

The correct approach in QV would be to use a date field from your data model to group your data, a year field as dimension or a calculated dimension using year(date) function on a date field. Then you would automatically get the sales per year.

Seems like I don't get what you want to achieve, could you post some sample lines of data and your requested outcome?

Re: valueloop in chart

sr_qlikview — Tue, 26 Feb 2013 23:08:52 GMT

vMaxyr = Max(year)

vLastyr = Max(year)-2

dimension defined:

=valueloop($(vMaxyr),$(vLastyr),1)

expression:

sum(sales)

anything wrong in syntax ? should i use any specific syntax in expression ?

requirement: when i select any year (ex: 2010) i wanted to get sales for past 3 yrs (2010,2009,2008) on a line chart.

Re: valueloop in chart

swuehl — Tue, 26 Feb 2013 23:14:25 GMT

I just wouldn't use a synthetic dimension for that kind of requirement.

Try your year field as dimension and your sum with a set expression:

=sum({<year = {">=$(vLastyr)<=$(vMaxyr)"}>} sales)

Re: valueloop in chart

sr_qlikview — Wed, 27 Feb 2013 20:43:08 GMT

sorry, i didn't work ... i have to create the same for year, month and qtr ... for month ... i have to use monthkey ( which is basically starts from 36 and goes back to 1) , when i select month 24 i need a chart for months 24 to 13 as dimension and sum of sales as expression. tried the metiod u mentioned didn't work

Re: valueloop in chart

swuehl — Wed, 27 Feb 2013 21:22:47 GMT

Please post a small sample application (or an Excel table with sample input data), it's much easier to see then what your are talking about.

Please also post a small description what you want to see (e.g. using an Excel table).

Re: valueloop in chart

sr_qlikview — Thu, 28 Feb 2013 21:39:36 GMT

thanks for your help, it worked with adding 1 as set selection.