topic Expression help in QlikView

Expression help

danielnevitt — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Expression help

sunny_talwar — Thu, 21 Apr 2016 15:23:34 GMT

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))

Re: Expression help

syukyo_zhu — Thu, 21 Apr 2016 15:33:29 GMT

Hi,

for 2nd requirement, try this

hope helpful

Re: Expression help

danielnevitt — Thu, 21 Apr 2016 15:36:50 GMT

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

Re: Expression help

sunny_talwar — Thu, 21 Apr 2016 15:44:44 GMT

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

Re: Expression help

danielnevitt — Thu, 21 Apr 2016 16:00:21 GMT

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

Re: Expression help

sunny_talwar — Thu, 21 Apr 2016 16:09:18 GMT

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))

Re: Expression help

danielnevitt — Fri, 22 Apr 2016 08:54:05 GMT

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

Re: Expression help

sunny_talwar — Fri, 22 Apr 2016 11:25:00 GMT

Not sure, but may be try this:

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

Re: Expression help

danielnevitt — Fri, 22 Apr 2016 12:34:57 GMT

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

Re: Expression help

sunny_talwar — Fri, 22 Apr 2016 12:36:50 GMT

One of the date fields or if there are 3 of them available (like in your example) less than which date? max of the three, min of the three?

Re: Expression help

danielnevitt — Fri, 22 Apr 2016 12:41:37 GMT

I require the expression to be less that the date field on that line. Does that make sense?

ie in the example the expression on the first line should be trade_dt <= 15/04/2016 and the second line should be trade_dt <= 18/04/2016 etc

Regards,

Daniel

Re: Expression help

sunny_talwar — Fri, 22 Apr 2016 12:51:01 GMT

For row level evaluation, set analysis is probably not going to work, may be you need to add Aggr() function in this case:

Sum(Aggr(If(trade_dt <= Date, fabs(pos_lot)), 'AllYourDimensionsHereSeperatedByComma + DateDimension'))

Re: Expression help

danielnevitt — Fri, 22 Apr 2016 12:56:07 GMT

Thanks for the reply Sunny.

I'm slightly unsure what you mean by 'AllYourDimensionsHereSeperatedByComma + DateDimension'?

Is there a way that the expression works based on whichever dates are selected in the Date column?

Regards,

Daniel

Re: Expression help

danielnevitt — Mon, 25 Apr 2016 10:10:19 GMT

Hi Sunny,

Were you able to look at my question below?

Thanks,

Daniel

Thanks for the reply Sunny.

I'm slightly unsure what you mean by 'AllYourDimensionsHereSeperatedByComma + DateDimension'?

Is there a way that the expression works based on whichever dates are selected in the Date column?

Regards,

Daniel

Re: Expression help

sunny_talwar — Mon, 25 Apr 2016 10:11:17 GMT

What all dimensions are there in your chart right now?

Re: Expression help

danielnevitt — Mon, 25 Apr 2016 10:15:56 GMT

Hi Sunny,

Dimensions are Date and ProductName.

I would then like to show the sum of pos_lots where the trade_dt is less than or equal to the Date in the row.

Hope that makes sense.

Regards,

Daniel

Re: Expression help

sunny_talwar — Mon, 25 Apr 2016 10:19:10 GMT

Not sure how the data is structured, but try this:

Sum(Aggr(If(trade_dt <= Date, fabs(pos_lot)), ProductName, Date, trade_dt))

Re: Expression help

danielnevitt — Mon, 25 Apr 2016 10:24:48 GMT

Hi Sunny,

Unfortunately that doesn't work. I am still getting the same results for each date.

Regards,

Daniel

Re: Expression help

sunny_talwar — Mon, 25 Apr 2016 10:29:11 GMT

I guess I will have difficult time suggesting anything without looking at it. Would you be able to share a sample?