Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Calculating previous year with Aggr and Distinct function

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

Re: Calculating previous year with Aggr and Distinct function

try this

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

kkkumar82
Valued Contributor III

Re: Calculating previous year with Aggr and Distinct function

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
Valued Contributor III

Re: Calculating previous year with Aggr and Distinct function

Check it with your data otherwise provide a sample

mackers758
New Contributor

Re: Calculating previous year with Aggr and Distinct function

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
New Contributor

Re: Calculating previous year with Aggr and Distinct function

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

kkkumar82
Valued Contributor III

Re: Calculating previous year with Aggr and Distinct function

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

MVP
MVP

Re: Calculating previous year with Aggr and Distinct function

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
New Contributor

Re: Calculating previous year with Aggr and Distinct function

Sunny, that has worked perfectly.

Many Thanks