Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nevilledhamsiri
Specialist
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

   

CUSTOMERYEAR SALES
A2017      15,000 50%
B2017      10,000 18%
C2017      12,500 -17%
D2017      14,000 0%
A2016      10,000 -80%
B2016         8,500 -66%
C2016      15,000 200%
D2016                -   -100%
A2015      50,000 0%
B2015      25,000 0%
C2015         5,000 0%
D2015         7,500 0%
1 Solution

Accepted Solutions
rubenmarin

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.

View solution in original post

4 Replies
rubenmarin

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.

rubenmarin

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

nevilledhamsiri
Specialist
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

rubenmarin

Both versions starts with Data in Inline:

Data:

LOAD * INLINE [

CUSTOMER, YEAR, AMOUNT

...

];

The Join version adds:

LastYear:

LOAD CUSTOMER,

  YEAR + 1 as YEAR,

  AMOUNT as LYAMOUNT

Resident Data;

Left Join (Data)

LOAD * Resident LastYear;

DROP Table LastYear;

The Concatenate version adds:

Concatenate (Data)

LOAD CUSTOMER,

  tmpYEAR as YEAR,

  LYAMOUNT

where Exists('YEAR', tmpYEAR);

LOAD CUSTOMER,

  YEAR + 1 as tmpYEAR,

  AMOUNT as LYAMOUNT

Resident Data;

Once you have LYAMOUNT field loaded, the expression can be:

=Alt((Sum(AMOUNT)-Sum(LYAMOUNT))

/Sum(LYAMOUNT), 0)