3 Replies Latest reply: Jul 8, 2012 5:51 AM by Qaiyum Shaikh RSS

    Help on displaying data

      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 DateTrade ValueTrade Volume
      02/15/201156789723456
      02/20/201176992734567
      03/10/2011890000038921
      04/05/20115345839512324
      04/23/20119469769035348539

       

      Display required:

      Apr 2011March 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
        • Re: Help on displaying data
          Jason Michaelides

          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