Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have 2 values Year and Total.
I have to display in my chart report like Year on Year growth %.How can i achieve this.
Thanks
Govind R
Generally it's calculated using something like this: ValueOfYear2 / ValueOfYear1 - 1
(Current Year - Previous Year)/Previous Year
for Growth %
Are you doing this with Year as your dimension? If you are, then you either need to use Above()/Below() functions, or you can use The As-Of Table to do it more efficiently.
I have 2 measures YEAR and TOTAL
How can i achieve this ?
Hi Sunny, I have a sample script below and using this expression: sum(Sales)- above(sum(Sales))
Can you please justify why there are few blank values and could you please provide me an example how to use above and below to calculate YOY%
load
date(date#(Date_Feild,'DD-MM-YYYY'),'DD-MM-YYYY') as Date_Feild,
Sales
INLINE [
Date_Feild, Sales
01-01-2015 ,10
01-02-2015 ,20
01-03-2015 ,10
01-04-2015 ,50
02-04-2015 ,100
03-04-2015 ,20
04-04-2015 ,30
01-05-2015 ,30
01-06-2015 ,30
01-07-2015 ,30
01-08-2015 ,30
09-08-2015 ,10
10-08-2015 ,10
01-01-2016 ,10
01-02-2016 ,20
01-03-2016 ,10
01-04-2016 ,30
02-04-2016 ,50
03-04-2016 ,10
04-04-2016 ,10
01-05-2016 ,30
01-06-2016 ,30
01-07-2016 ,30
01-08-2016 ,30
09-08-2016 ,10
10-08-2016 ,10
];
Since we are looking at dates, the above()/below() may not be perfect and with missing entries, it can further complicate. Use As-Of Table is a better approach in this case.
Script:
Table:
LOAD Date(Date#(Date_Field,'DD-MM-YYYY'),'DD-MM-YYYY') as Date_Field,
Sales
INLINE [
Date_Field, Sales
01-01-2015 ,10
01-02-2015 ,20
01-03-2015 ,10
01-04-2015 ,50
02-04-2015 ,100
03-04-2015 ,20
04-04-2015 ,30
01-05-2015 ,30
01-06-2015 ,30
01-07-2015 ,30
01-08-2015 ,30
09-08-2015 ,10
10-08-2015 ,10
01-01-2016 ,10
01-02-2016 ,20
01-03-2016 ,10
01-04-2016 ,30
02-04-2016 ,50
03-04-2016 ,10
04-04-2016 ,10
01-05-2016 ,30
01-06-2016 ,30
01-07-2016 ,30
01-08-2016 ,30
09-08-2016 ,10
10-08-2016 ,10
];
LinkTable:
LOAD Date_Field as Report_Date_Field,
Date_Field,
'CY' as Flag
Resident Table;
Concatenate(LinkTable)
LOAD Date_Field as Report_Date_Field,
Date(AddYears(Date_Field, -1), 'DD-MM-YYYY') as Date_Field,
'PY' as Flag
Resident Table;