Need help with below logic

[PrashantSangle]

Dear All

I have attached my qvw file and xls file

also provide required logic inside it

Please help me to resolve it.

Let me know if you required any additional information.

It will be really helpful if got solution in script level.

Regards,

[avinashelite]

Hi ,

One quick question for a single LINE_ID you have multiple Start_Date and End_Date so which date we need to use to compare with the start date

[PrashantSangle]

Hi,

in 1st if start date is come in between start_date and End_date then pick that operand

else check start date is greater than or equal to start_Date and end_date is null then pick that operand

if both case fail then check start date is not equal to creation date

and then compare creation date with start_date and End_Date

I hope you got the logic.

Regards

[PrashantSangle]

[cwolf]

For your logic, the expression is correct.

These two lines will produce the result for ID 3386771

The expression some clearer:

=sum(IF([Start Date] >= Start_date AND ([Start Date] <= End_Date or ISNULL(End_Date)),OPERAND,

IF([Creation Date] <> [Start Date],IF([Creation Date] >= Start_date AND ([Creation Date] <= End_Date or ISNULL(End_Date)),OPERAND))

))

[Kushal_Chawda]

Hi,

As per the data the logic which you are applying is satisfying two conditions

1) If [start date] in between start_date and End_date, Operand  - 1635.33

2) if[creation date] in between start_date and End_date then Operand - 1692.48

So 1) +2) = 3327.81 what you are getting is correct for Line Id 3386771

[avinashelite]

try this expression , I got the exact result

=sum(IF(([Start Date] >=Start_date) AND ([Start Date] <=End_Date),OPERAND,

  IF(([Start Date] >= Start_date) AND len(trim(End_Date))=0,OPERAND,

  IF([Creation Date] <> [Start Date],

  IF(([Creation Date] >=Start_date) AND ([Creation Date] <End_Date),OPERAND,

  IF(([Creation Date] >= Start_date) AND len(trim(End_Date))=0,OPERAND))))))

[avinashelite · Accepted solution]

Please find the attachment for the solution:

[PrashantSangle]

Hi,

Initially I thought the same but my concern is

if my 1st if condition is true then why it is calculating value from else part.

I don't find it but I think I am missing something, but not able to find it.

I hope you got my point.

Thanks for the help.

Regards

[PrashantSangle]

Hi,

Initially I thought the same but my concern is

if my 1st if condition is true then why it is calculating value from else part.

I don't find it but I think I am missing something, but not able to find it.

I hope you got my point.

Thanks for the help.

Regards