# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
cancel
Showing results for
Did you mean:
Specialist

## Year on Year Growth

Hi,

Please write an expression to calculate growth for 2017 & 2016 over the data presented. The excel too attached.

Regards

Neville

 CUSTOMER YEAR SALES A 2017 15,000 50% B 2017 10,000 18% C 2017 12,500 -17% D 2017 14,000 0% A 2016 10,000 -80% B 2016 8,500 -66% C 2016 15,000 200% D 2016 - -100% A 2015 50,000 0% B 2015 25,000 0% C 2015 5,000 0% D 2015 7,500 0%
1 Solution

Accepted Solutions
MVP

Hi, with this sample data this expression can work:

=Alt((Sum(AMOUNT)-Below(TOTAL Sum(AMOUNT), Count(DISTINCT TOTAL <YEAR> CUSTOMER)))

/Below(TOTAL Sum(AMOUNT), Count(DISTINCT TOTAL <YEAR> CUSTOMER)), 0)

But I'm not sure if this will work with real data, another option can be adding the data for last year as a new field in the data, so each record has all needed data to calculate the growth, this should give better performance.

4 Replies
MVP

Hi, with this sample data this expression can work:

=Alt((Sum(AMOUNT)-Below(TOTAL Sum(AMOUNT), Count(DISTINCT TOTAL <YEAR> CUSTOMER)))

/Below(TOTAL Sum(AMOUNT), Count(DISTINCT TOTAL <YEAR> CUSTOMER)), 0)

But I'm not sure if this will work with real data, another option can be adding the data for last year as a new field in the data, so each record has all needed data to calculate the growth, this should give better performance.

MVP

Another option to add last year data using concatenate instead of join

Specialist
Author

Since I have personnel edition, qvw file you sent wont work, appreciate if you send me a sample data how it is done!

Regards

Neville

MVP

Both versions starts with Data in Inline:

Data:

CUSTOMER, YEAR, AMOUNT

...

];

LastYear:

YEAR + 1 as YEAR,

AMOUNT as LYAMOUNT

Resident Data;

Left Join (Data)

DROP Table LastYear;

Concatenate (Data)

tmpYEAR as YEAR,

LYAMOUNT

where Exists('YEAR', tmpYEAR);