Need to remove Dimension

[kalyandg]

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

[Anonymous]

send me the expressions you are using in Amount and Bonus

[kalyandg]

hi,

for amount: its simple sum(amount)

for bonus: sum(amount)*(30-day(duedate)+1) + sum(amount)*((floor(DueDate-Transactiondate)/30)-1)

tis is my calculation

[Anonymous]

Try this anc confirm

sum(amount)*(30-day(Duedate)+1) + sum(amount)*((floor(DueDate-Transactiondate)/30)-1

[tresesco]

Re-write expression like:

=Sum(amount*(30-day(duedate)+1) + Sum(amount*((floor(DueDate-Transactiondate)/30)-1))

[PrashantSangle]

Hi,

Your expression is not going to work.

Because,When you remove Transaction Date and Due Date From Your Dimension.

your expression failed to evaluate which Transaction Date Out of 3 in your case and

Which Due Date out of 3 need to be take.

Basically your expression is working on detail level not on Summary Level.

My suggestion is use Set analysis in calculations.

Regards,

[kalyandg · Accepted solution]

Hi All,

The below expression is worked out.

(0.06)* sum(Amount *

         (

         1/360*(30-day(duedate)+1) +

         1/12*(Floor(((End)-(duedate))/30)-1) +

         1/360*(day(End)-1)

         )

   ))

I  thank Mr.HIC who gave the idea.

Best Regards,

Kalyan.D