Announcements
cancel
Showing results for
Did you mean:
Contributor

## Calculating previous year with Aggr and Distinct function

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?

1 Solution

Accepted Solutions
MVP

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

8 Replies

try this

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

Specialist III

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

Specialist III

Check it with your data otherwise provide a sample

Contributor
Author

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.

Contributor
Author

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

Specialist III

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.

Year, Sales

2014,  1500

2015,  1700

2016,  1800

2017,  1900

];

Now try my expression in a text objects

MVP

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

Contributor
Author

Sunny, that has worked perfectly.

Many Thanks