Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi gurus,
I am preparing some statistical report and able to display the data using text box object. I would like to know if it is possible to display the data using straight table or pivot table and how to decide on dimensions. My data looks like this:
Raw Data:
Trade Date | Trade Value | Trade Volume |
---|---|---|
02/15/2011 | 567897 | 23456 |
02/20/2011 | 769927 | 34567 |
03/10/2011 | 8900000 | 38921 |
04/05/2011 | 53458395 | 12324 |
04/23/2011 | 9469769035 | 348539 |
Display required:
Apr 2011 | March 2011 | % change | |
---|---|---|---|
Trade Value | <sum of trade value in apr> | <sum of trade value in mar> | change between apr and march trade value |
Trade Volume | <sum of trade volume in apr> | <sum of trade volume in mar> | change betweeapr and march |
(Oops - I got the 2 labels mixed up! Switch them around...)
I'm going to assume that you only ever want to see two months' variations, but that you may select different months as a start point...
In your script:
Data:
CrossTable(MeasureType,Amount,3)
LOAD
Date,
MonthStart(Date) AS MonthStart,
MonthEnd(Date) AS MonthEnd,
TradeVolume,
TradeValue
From...your data source...;
Then, in your straight table chart, use MeasureType as the dimension and add 3 expressions:
Sum({<Date = {"<=$(Max(MonthEnd)) >=$(Max(MonthStart))"}>} Amount) As your label, use =Date(AddMonths(Max(MonthStart),-1),'MMM YYYY')
Sum({<Date = {"<=$(=AddMonths(Max(MonthEnd)),-1) >=$(=AddMonths(Max(MonthStart)),-1)"}>} Amount) As your label, use =Date(Max(MonthStart),'MMM YYYY')
Column(1)-Column(2)
That should work if I've got the syntax right...
Hope this helps,
Jason
(Oops - I got the 2 labels mixed up! Switch them around...)
Thanks, Jason it worked for me.