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: 
Not applicable

current &Previous yr quarterly sales in the same table

Hi all,

Reference the table below:

I want to add the 4th Column "Last yr same qtr sales" which will show the sales of the qtr but previous year.

Any Idea what would be the code for this?

  

YearQuarterSalesLast yr same qtr sales
2012Q19665x
2012Q22750x
2012Q32191x
2012Q43316x
2013Q12656x
2013Q22338x
2013Q37551x
2013Q45173x
2014Q13360x
2014Q26958x
2014Q38052x
2014Q44819x
2015Q136333360
2015Q218906958
2015Q376658052
2015Q450554819

Thanks in advance.

Mike

3 Replies
sunny_talwar

Is this something you need from the script or front end?

Not applicable
Author

Hi Sunny,

I am looking for a front end solution for this problem.

Is there any formula trick that I can use to display sales figure 2014 Q4, in the last row (of 2015 Q4)?

sunny_talwar

Here are the two options you have.

Option 1 will require a link table, so a little manipulation in the script. But I think it is little better than the option2

Option 2 will use Above function

Capture.PNG

Script for Option 1:

Table:

LOAD Year,

    Quarter,

    Date#(Year&Num(Right(Quarter, 1)*3, '00'), 'YYYYMM') as YearQuarter,

    Sales

FROM

[https://community.qlik.com/thread/224064]

(html, codepage is 1252, embedded labels, table is @1);

LinkTable:

LOAD YearQuarter as ReportingYearQuarter,

  'Q' & Ceil(Month(YearQuarter)/3) as ReportingQuarter,

  Year(YearQuarter) as ReportingYear,

  YearQuarter,

  'CY' as Flag

Resident Table;

Concatenate (LinkTable)

LOAD YearQuarter as ReportingYearQuarter,

  'Q' & Ceil(Month(YearQuarter)/3) as ReportingQuarter,

  Year(YearQuarter) as ReportingYear,

  AddYears(YearQuarter, -1) as YearQuarter,

  'PY' as Flag

Resident Table;

Script for Option 2:

Table:

LOAD Year,

    Quarter,

    Sales

FROM

[https://community.qlik.com/thread/224064]

(html, codepage is 1252, embedded labels, table is @1);