valueloop in chart

[sr_qlikview]

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??

[swuehl]

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')

[sr_qlikview]

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

[swuehl]

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?

[sr_qlikview]

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.

[swuehl · Accepted solution]

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)

[sr_qlikview]

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

[swuehl]

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).

[sr_qlikview]

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