Calculate Sales Sales Values per Year

[#1]

Hi

I am trying to build my first QliView to demonstrate to my company however I am stumbling at what should be a simple step.

I have a source excel table that has sales data for 2012 2013 and 2014.  I would like to build a Pivot table that has the first column as a Cyclic Drill(containing Month, Qtr,  and the other columns to show the Sales Current Year, Previous Year 1, Previous Year 2 etc.

Can anyone assist an advise the best way to go about this as an expression.  I have tried the AGGR(SUM(.... function but this does not work.

I have attached a test QVW file and the source XLS, also in sheet2 of the XLS I have created a mock up of what I am trying to achieve.

Many thanks for any assistance that can be provided.

[manojkulkarni]

Pls find attachment. I have tried to address ur issue.

[#3]

Hi

Thank you for the fast response, I only have a personal edition license and cannot open the qvw you sent.    Any idea how I can open this??

[sasikanth]

for currrent year

use

let vYear=max(year_colum)

sum({<year_colum={'&(=vYear)'}>}InVet)

sum({<year_colum={'&(=vYear-1)'}>}InVet)

sum({<year_colum={'&(=vYear-2)'}>}InVet)

[#5]

Hi

I must be doing something wrong from the above.

I have created a variable vYear    =max(year_colum)

Then created the expression =sum({<year_colum={'&(=vYear)'}>}InvNet)

Many thanks

[sasikanth]

year_column means column name --> here you have to use "Year"

[#7]

Still no luck, what am I missing???

Expression say all OK but I do not get a result.

Thank you

[#8]

Hi Manjol

I am still struggling with this one, can you describe how you approached this, did you try a different method to Sasikanth?

thanks

[sasikanth · Accepted solution]

do it directlt like below

=sum({<year={'2004'}>}InvNet)

=sum({<year={'2005'}>}InvNet)

=sum({<year={'2006'}>}InvNet)