topic Re: Need to remove Dimension in QlikView

Need to remove Dimension

kalyandg — Thu, 19 Jun 2014 10:40:06 GMT

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

Re: Need to remove Dimension

Anonymous — Thu, 19 Jun 2014 10:41:22 GMT

send me the expressions you are using in Amount and Bonus

Re: Need to remove Dimension

kalyandg — Thu, 19 Jun 2014 11:47:07 GMT

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

Re: Need to remove Dimension

Anonymous — Thu, 19 Jun 2014 11:50:31 GMT

Try this anc confirm

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

Re: Need to remove Dimension

tresB — Thu, 19 Jun 2014 11:52:37 GMT

Re-write expression like:

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

Re: Need to remove Dimension

PrashantSangle — Sat, 21 Jun 2014 08:07:46 GMT

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,

Re: Need to remove Dimension

kalyandg — Thu, 26 Jun 2014 10:36:29 GMT

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