compare the past 3 years salaries

[Kohli]

I have table like these 

LineNO              EmployeeID          StartDate           EndDate                sales

1                   1.00                1/1/2008 00:00:00   31/12/2008 00:00:00 100000    

2                   1.00                1/1/2009 00:00:00   31/12/2009 00:00:00 110000    

3                   1.00                1/1/2010 00:00:00   31/12/2010 00:00:00 120000    

4                   1.00                1/1/2011 00:00:00   31/12/2011 00:00:00 130000    

5                   1.00                1/1/2012 00:00:00   31/12/2012 00:00:00 140000    

6                   1.00                1/1/2013 00:00:00   31/12/2013 00:00:00 150000    

7                   1.00                1/1/2014 00:00:00                                     160000    

8                   2.00                1/4/2010 00:00:00   31/12/2010 00:00:00 35000     

9                   2.00                1/1/2011 00:00:00   31/12/2011 00:00:00 36050     

10                  2.00                1/1/2012 00:00:00   31/12/2012 00:00:00 37132     

11                  2.00                1/1/2013 00:00:00   31/12/2013 00:00:00 38246     

12                  2.00                1/1/2014 00:00:00                                      39393     

13                  3.00                1/1/2008 00:00:00   31/12/2008 00:00:00 65000     

14                  3.00                1/1/2009 00:00:00   31/12/2009 00:00:00 75000     

15                  3.00                1/1/2010 00:00:00   31/12/2010 00:00:00 77250     

16                  3.00                1/1/2011 00:00:00   31/12/2011 00:00:00 79568     

17                  3.00                1/1/2012 00:00:00   31/12/2012 00:00:00 81955     

18                  3.00                1/1/2013 00:00:00   31/12/2013 00:00:00 84414     

19                  3.00                1/1/2014 00:00:00                                      86946     

i want compare the past 3 years sales...

output like:

EmpID, 2012,2013,2014

1,  140000,150000'160000

2, 37312,38246, 39393

.....

can you please write the script for that,

[jonathandienst]

Add a Year field during your load:

LOAD LineNo,

     EmployeeID,

    Year(StartDate) as Year,

     ...

Then use Year and EmployeeID as dimensions and Sum(sales) as the expression in a pivot table. Click and drag the Year dimension to the top of the pivot table.

[Kohli]

i have 2011,2012,2013, 2104 and 2015 years in Years field. I want only 2015,2014,2013 years .sales in same pivot table

[Kohli]

iam trying this one also. but it is coming all years

Sum({<YEAR={">=$(=YEAR(Max([Sal StartYear]),-3))<=$(=Max(Sal StartYear))"}>} Sales)

[sushantwanjari]

Hi,

Check if the date fields are loaded properly.

The following expression worked for me

=SUM( {<Year_Start = {"> $(=Max(TOTAL Year_Start) -3) <= $(=Max(TOTAL Year_Start)) "}>} sales)

Hope this helps you..

-Sushant

[sergio0592]

Try with :

Sum({<YEAR={">=$(=YEAR(Max([Sal StartYear]))-3)<=$(=Max(Sal StartYear))"}>} Sales)

[vishsaggi]

May be you can try like:

= Sum({< YearField = {">=$(=Year(Today())-3)"} >}DollarAmount)

OR this:

= Sum({< YearField = {">$(=Max(YearField)-3)"} >}sales)

[Anonymous]

Hi,

You can use below script:

Test:

LOAD

     max(year(StartDate)) as MaxYear

FROM

[..\Test_Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

LET vMaxYear = peek('MaxYear',0);

[Final Data]:

LOAD LineNO,

     EmployeeID,

     StartDate,

     EndDate,

     sales,

     year(StartDate) as Year

FROM

[..\Test_Data.xlsx]

(ooxml, embedded labels, table is Sheet1)

where year(StartDate) > $(vMaxYear)-3;

This will give you only last 3 years.