Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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% |
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.
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.
Another option to add last year data using concatenate instead of join
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
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)