Calculating previous year with Aggr and Distinct function

[mackers758]

Hi,  newbie here.

I'm trying to calculate totals for the previous year to what I have selected, for comparison purpose.

To obtain the data for the year that I have selected I use the following:

SUM(AGGR(SUM({$ <[Date.autoCalendar.Year] = {"$(=maxstring([Date.autoCalendar.Year]))"}>}DISTINCT TEU), JH_JobNum))

This gives me the sum of the TEU for each JH_JobNum, for the year that I have chosen.

However I would also like to diaply the previous years data for comparison.

I have tried a multitude of lines but failing to get the data.

I thought that the following would have provded me with the data, but not playing ball:

SUM(AGGR(SUM({$ <[Date.autoCalendar.Year] = {"$(=maxstring([Date.autoCalendar.Year])-1)"}>}DISTINCT TEU), JH_JobNum))

Could anyone shed some light on where I'm going wrong?

[Kushal_Chawda]

try this

SUM({<[Date.autoCalendar.Year]>}AGGR(SUM({$ <[Date.autoCalendar.Year] = {"$(=max([Date.autoCalendar.Year])-1)"}>}DISTINCT TEU), JH_JobNum))

[kkkumar82]

Selected Year : Sum({<Year = {"$(=Max(Year))"}>}Sales)

Previous Year: Sum({<Year = {"$(=Max(Year)-1)"}>}Sales)

These two expression with out selections will give you the sales of Max Year and its previous Year but if you select any year then it gives selected Year and Previous Year Sales

[kkkumar82]

Check it with your data otherwise provide a sample

[mackers758]

Kiran, many thanks for the prompt reply.

I have used your syntax and found that I must select both years to obtain the previous years data at the same time as the year I want to see data for.

Therefore if I wanted to see the primary data for 2016 and just wanted to see secondary data for 2015 I must select both 2015 and 16.

[mackers758]

Kushal, I've already tried that. Sorry didn't work.

[kkkumar82]

I think something went wrong the expressions I have given you are for eg, in real with out selections the first expression gives the sales for the Max year and second one gives for previous year.

Load * Inline [

Year, Sales

2014,  1500

2015,  1700

2016,  1800

2017,  1900

];

Now try my expression in a text objects

[sunny_talwar · Accepted solution]

May be try this

Sum({<[Date.autoCalendar.Year] = {"$(=Max([Date.autoCalendar.Year])-1)"}>} Aggr(Sum({<[Date.autoCalendar.Year] = {"$(=Max([Date.autoCalendar.Year])-1)"}>} DISTINCT TEU), JH_JobNum))

[mackers758]

Sunny, that has worked perfectly.

Many Thanks