qtr comparisoin

[soha1902]

Hi,

I want to compare current year quarter vs previous year quarter values:

QTR_ID	Sales
2013Q1	100
2013Q2	200
2013Q3	300
2013Q4	150
2014Q1	200
2014Q2	110
2014Q3	120
2014Q4	140
2015Q1	160
2015Q2	300
2015Q3	500
2015Q4	400

for eg 2015Q4 vs 2014Q4

Can any one help me to create a tabular report for this???

[Anonymous]

refer below link.....

Re: Curr qtr vs prev qtr

[its_anandrjs]

You can try this way also

LOAD *,Left(QTR_ID,4) as Year,Right(QTR_ID,1) as QtrMax;

LOAD * Inline

[

QTR_ID, Sales

2013Q1, 100

2013Q2, 200

2013Q3, 300

2013Q4, 150

2014Q1, 200

2014Q2, 110

2014Q3, 120

2014Q4, 140

2015Q1, 160

2015Q2, 300

2015Q3, 500

2015Q4, 400

];

In any straight table

Dim:- QTR_ID

Expre:- sum( {<Year= {"$(=max(Left(QTR_ID,4)))"},QtrMax ={"$(=max(right(QTR_ID,1)))"} >}Sales)

Expre:- sum( {<Year= {"$(=max(Left(QTR_ID,4))-1)"},QtrMax ={"$(=max(right(QTR_ID,1))-1)"} >}Sales)

Regards

Anand

[soha1902]

What is this "QtrMax" field?? It is not available.

[its_anandrjs]

It is calculated field to finding max quarter value in filed QtrMax

Regards

Anand

[its_anandrjs]

Try this expression please with some changes

Dim:- QTR_ID

Expre:- sum( {<Year= {"<=$(=max(Left(QTR_ID,4))) >=$(=max(Left(QTR_ID,4))-1)"},QtrMax ={"$(=max(right(QTR_ID,1)))"} >}Sales)

Regards,

Anand

[jagan]

Hi,

Try like this

LOAD

*,

Left(QTR_ID,4) AS Year,

MakeDate(Left(QTR_ID,4), Right(QTR_ID,1) * 3) AS Date

FROM DataSource;

Now use below expressions:

Current Quarter:

Sum({<Year=, Date={'>=$(=QuarterStart(Max(Date)))<=$(=QuarterEnd(Max(Date)))'}>} Sales)

Previous Quarter:

Sum({<Year=, Date={'>=$(=QuarterStart(Max(Date), -1))<=$(=QuarterEnd(Max(Date), -1))'}>} Sales)

Hope this helps you.

Regards,

Jagan.

[sunny_talwar]

Something like this?

Script:

Table:

LOAD Dual(QTR_ID, PurgeChar(QTR_ID, 'Q')) as QTR_ID,

  Sales

Inline [

QTR_ID, Sales

2013Q1, 100

2013Q2, 200

2013Q3, 300

2013Q4, 150

2014Q1, 200

2014Q2, 110

2014Q3, 120

2014Q4, 140

2015Q1, 160

2015Q2, 300

2015Q3, 500

2015Q4, 400

];

NewTable:

LOAD *,

  AutoNumber(QTR_ID) as Sort

Resident Table

Order By QTR_ID;

DROP Table Table;

[As-Of Table]:

LOAD QTR_ID as [AS-OF],

  Sort,

  'CQ' as Flag

Resident NewTable;

Concatenate ([As-Of Table])

LOAD QTR_ID as [AS-OF],

  Sort - 1 as Sort,

  'PQ' as Flag

Resident NewTable;

Expression for current quarter: =Sum({<Flag = {'CQ'}>}Sales)

Expression for last quarter: =Sum({<Flag = {'PQ'}>}Sales)

[jagan]

For getting last year same quarter use

Sum({<Year=, Date={'>=$(=QuarterStart(Max(Date), -4))<=$(=QuarterEnd(Max(Date), -4))'}>} Sales)

Regards,

jagan.