Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 03 years 2013, 2015 & 2015
If I select each year separate it gives variation for 04 quarters for that year only using "Sum - Above Sum"
But when I select Q1 for all years it is not working.
I want to see variation for Ist quarter only with all years selected.
Any help much appreciated.
Best Regards,
Khayam
Script:
Table:
LOAD *,
Dual(Year & '-' & Quarter, (Year & Num(PurgeChar(Quarter, 'Q'), '00')) * 1) as YearQuarter;
LOAD * Inline [
Year, Quarter, Sales, Other Income, Total Revenue
2016, Q1, 309521100, 35965098, 345486198
2015, Q1, 159521100, 20965098, 180486198
2015, Q2, 196986777, 41820906, 238807683
2015, Q3, 127346264, 46353603, 173699867
2015, Q4, 164863567, 40056814, 204920381
2014, Q1, 115799604, 14235830, 130035434
2014, Q2, 174834180, 25373479, 200207659
2014, Q3, 107193564, 27688570, 134882134
2014, Q4, 143139135, 32749869, 175889004
];
LinkTable:
LOAD YearQuarter as ReportingYearQuarter,
YearQuarter,
'CYQ' as Flag
Resident Table;
Concatenate (LinkTable)
LOAD YearQuarter as ReportingYearQuarter,
YearQuarter - 100 as YearQuarter,
'PYQ' as Flag
Resident Table;
Use AsOfTable approach mentioned here: The As-Of Table
Would you be able to provide a sample?
Please find below the sample data.
I need to compare sales variation in % for Q1 only for 2014, 2015 and 2016 (i.e if Q1 is selected only)
Sum - Above Sum works when Q1 to Q4 are selected and also single year is selected but not for Q1 for other years selected.
Regards,
Khayam
Quarter Sales Other Income Total Revenue
2016 Q1 309,521,100 35,965,098 345,486,198
2015 Q1 159,521,100 20,965,098 180,486,198
2015 Q2 196,986,777 41,820,906 238,807,683
2015 Q3 127,346,264 46,353,603 173,699,867
2015 Q4 164,863,567 40,056,814 204,920,381
2014 Q1 115,799,604 14,235,830 130,035,434
2014 Q2 174,834,180 25,373,479 200,207,659
2014 Q3 107,193,564 27,688,570 134,882,134
2014 Q4 143,139,135 32,749,869 175,889,004
Sorry the above data contains separate fields for year and Quarter as below:
Year Quarter Sales Other Income Total Revenue
2016 Q1 309,521,100 35,965,098 345,486,198
2015 Q1 159,521,100 20,965,098 180,486,198
2015 Q2 196,986,777 41,820,906 238,807,683
2015 Q3 127,346,264 46,353,603 173,699,867
2015 Q4 164,863,567 40,056,814 204,920,381
2014 Q1 115,799,604 14,235,830 130,035,434
2014 Q2 174,834,180 25,373,479 200,207,659
2014 Q3 107,193,564 27,688,570 134,882,134
2014 Q4 143,139,135 32,749,869 175,889,004
Script:
Table:
LOAD *,
Dual(Year & '-' & Quarter, (Year & Num(PurgeChar(Quarter, 'Q'), '00')) * 1) as YearQuarter;
LOAD * Inline [
Year, Quarter, Sales, Other Income, Total Revenue
2016, Q1, 309521100, 35965098, 345486198
2015, Q1, 159521100, 20965098, 180486198
2015, Q2, 196986777, 41820906, 238807683
2015, Q3, 127346264, 46353603, 173699867
2015, Q4, 164863567, 40056814, 204920381
2014, Q1, 115799604, 14235830, 130035434
2014, Q2, 174834180, 25373479, 200207659
2014, Q3, 107193564, 27688570, 134882134
2014, Q4, 143139135, 32749869, 175889004
];
LinkTable:
LOAD YearQuarter as ReportingYearQuarter,
YearQuarter,
'CYQ' as Flag
Resident Table;
Concatenate (LinkTable)
LOAD YearQuarter as ReportingYearQuarter,
YearQuarter - 100 as YearQuarter,
'PYQ' as Flag
Resident Table;
Thank you so much !!!
Its not working when I loaded table from Excel worksheet.
Error >>> Link Table not found.
How the script will be if I have to load data from Excel file lets say its name is "Main".
Regards
give the two expressions in the staright table
SUM({<Year={$(max(Year))},Quarter={$(max(Quarter))}>}SALES)
SUM({<Year={$(max(Year)-1)},Quarter={$(max(Quarter))}>}SALES)
I guess you just need to change the name of the table in the resident load
Table:
LOAD *,
Dual(Year & '-' & Quarter, (Year & Num(PurgeChar(Quarter, 'Q'), '00')) * 1) as YearQuarter;
LOAD * Inline [
Year, Quarter, Sales, Other Income, Total Revenue
2016, Q1, 309521100, 35965098, 345486198
2015, Q1, 159521100, 20965098, 180486198
2015, Q2, 196986777, 41820906, 238807683
2015, Q3, 127346264, 46353603, 173699867
2015, Q4, 164863567, 40056814, 204920381
2014, Q1, 115799604, 14235830, 130035434
2014, Q2, 174834180, 25373479, 200207659
2014, Q3, 107193564, 27688570, 134882134
2014, Q4, 143139135, 32749869, 175889004
];
LinkTable:
LOAD YearQuarter as ReportingYearQuarter,
YearQuarter,
'CYQ' as Flag
Resident Main;
Concatenate (LinkTable)
LOAD YearQuarter as ReportingYearQuarter,
YearQuarter - 100 as YearQuarter,
'PYQ' as Flag
Resident Main;
Its not working
same error