Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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???
refer below link.....
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
What is this "QtrMax" field?? It is not available.
It is calculated field to finding max quarter value in filed QtrMax
Regards
Anand
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
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.
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)
For getting last year same quarter use
Sum({<Year=, Date={'>=$(=QuarterStart(Max(Date), -4))<=$(=QuarterEnd(Max(Date), -4))'}>} Sales)
Regards,
jagan.