Expression help

[danielnevitt]

Hi,

I have the below expression that I would like to make absolute.  For example rather than netting the pos_lots, I would like the absolute sum.  Is this possible?

Sum(If(trade_dt >='$(vYesterday)',pos_lots))

Also I have the below expression and I would like to add to the expression to only show values greater than 0.10.  Is this possible as well?

Count(if (OrderStatus='Cancelled',OrderStatus))/Count(TOTAL <OriginatorUserID> OrderStatus)

Regards,

Daniel

[sunny_talwar · Accepted solution]

May be this for the 1st requirement

Sum(If(trade_dt >='$(vYesterday)', fabs(pos_lots)))

2nd requirement

If(Count(if (OrderStatus='Cancelled',OrderStatus))/Count(TOTAL <OriginatorUserID> OrderStatus) > 0.10,

Count(if (OrderStatus='Cancelled',OrderStatus))/Count(TOTAL <OriginatorUserID> OrderStatus))

[syukyo_zhu]

Hi,

for 2nd requirement, try this

hope helpful

[danielnevitt]

Thanks Sunny.

For the 2nd expression, would it be possible to add variable dates?  ie so I can set the expression between Date 1 and Date 2?

Regards,

Daniel

[sunny_talwar]

Not sure what is the condition going to look at. Would you be able to share more details?

[danielnevitt]

Hi Sunny,

Sorry I probably wasn't clear.  I would like to add the variable vYesterday to both parts of the 2nd Expression.

Regards,

Daniel

[sunny_talwar]

May be something like this:

If(Count({<Date = {"$(vYesterday)"}>} if(OrderStatus='Cancelled',OrderStatus))/Count({<Date = {"$(vYesterday)"}>} TOTAL <OriginatorUserID> OrderStatus) > 0.10,

Count({<Date = {"$(vYesterday)"}>} if(OrderStatus='Cancelled',OrderStatus))/Count({<Date = {"$(vYesterday)"}>}TOTAL <OriginatorUserID> OrderStatus))

[danielnevitt]

Thanks Sunny, I have managed to resolve this issue.

Last question I have, is it possible to set the expression to use the date from another field?

The attached screen shot shows 'Date' field with values 15/04/2016, 18/04/2016 and 19/04/2016.

The 'Expression' field is: Sum(If(trade_dt >='$(vYesterday)', fabs(pos_lots)))

Is it possible to replace vYesterday with the date from the Date field?

Thanks,

Daniel

[sunny_talwar]

Not sure, but may be try this:

Sum({<trade_dt = p(Date)>}fabs(pos_lots))

[danielnevitt]

Thanks Sunny, unfortunately that didn't work.

Is it possible to show the sum of the absolute pos_lots when the trade_dt is less than or equal to the Date field?

Thanks,

Daniel