topic Re: Calculate Sales Sales Values per Year in QlikView

Calculate Sales Sales Values per Year

Thu, 26 Jun 2014 09:42:25 GMT

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.

Re: Calculate Sales Sales Values per Year

manojkulkarni — Thu, 26 Jun 2014 09:53:47 GMT

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

Re: Calculate Sales Sales Values per Year

Thu, 26 Jun 2014 10:06:20 GMT

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

Re: Calculate Sales Sales Values per Year

sasikanth — Thu, 26 Jun 2014 10:12:57 GMT

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)

Re: Calculate Sales Sales Values per Year

Thu, 26 Jun 2014 10:41:11 GMT

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

Re: Calculate Sales Sales Values per Year

sasikanth — Thu, 26 Jun 2014 10:43:27 GMT

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

Re: Calculate Sales Sales Values per Year

Thu, 26 Jun 2014 11:03:46 GMT

Still no luck, what am I missing???

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

Thank you

Re: Calculate Sales Sales Values per Year

Thu, 26 Jun 2014 13:04:13 GMT

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

Re: Calculate Sales Sales Values per Year

sasikanth — Fri, 27 Jun 2014 05:37:09 GMT

do it directlt like below

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

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

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