6 Replies Latest reply: Jun 26, 2014 6:37 AM by Kalyana Sundaram RSS

    Need to remove Dimension

    Kalyana Sundaram

      Hi all,

       

      I have built report like the below

       

      Branch TransactionDate DueDate       Amount   Bonus

      India       01/04/2014      01/04/2014     1000       50

      India       02/05/2014      01/05/2014     1500       100

      India       03/06/2014      01/06/2014      500        10

      China .............................

       

      Here i used transactiondate and duedate fields in bonus calculation. If I remove date dimensions, bonus becomes 0

      But it should not be like that.

       

      I need the format as below, no dates in dimension

       

      Branch Amount Bonus

      India     3000      160

       

      am having on issue, i have two date columns Transaction date and Duedate,

      my report is calculated using Start and End date,

       

      the following expression works, if date is present as dimension,

       

      sum(amount)*(0.06)/360*(30-day(duedate)+1)

      +

      sum(amount)*(0.06)/12*((floor(End-DueDate)/30)-1)

      +

      sum(amount)*(0.06)/360*(day(End)-1)

       

      if date is removed from dimension, the above calculation not works,

       

      30-day(duedate)+1 is calculated in script, so no problem when i use field,

      day(End)-1 is calculated in script, so no problem

      (floor((End-Duedate))/30)-1 is not calculated in script, because, End is date, and Duedate is date but from different table.

       

      i need the report as summary level not as a detailed...

       

      please help me to solve the above issue.

       

      Awaiting for help.

       

      Thanks and Regards,

      Kalyan.D