How to compare Same Quarters of Differennt years?

[300sss300]

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

sunindia‌ brunobertels‌

[sunny_talwar]

Use AsOfTable approach mentioned here: The As-Of Table

Would you be able to provide a sample?

[300sss300]

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

[300sss300]

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

[sunny_talwar · Accepted solution]

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;

[300sss300]

Thank you so much !!!

[300sss300]

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

[Anonymous]

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)

[sunny_talwar]

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;

[300sss300]

Its not working

same error