topic Re: Expression Not Working in QlikView

Expression Not Working

Anonymous — Tue, 29 Sep 2015 16:12:48 GMT

I am using this expression in Chart Dimension(Calculated Dimension) :

If((not isnull(Revenue) and (Revenue)<>0),'Won',if( (IsNull(Revenue) and PriceExpirationDate < Today()),'Lost','Open'))

I have three fields: Revenue,Price Expiration Date and Status.

1) If the revenue has a value,irrespective of price expiration date the status will be 'won'

2)If the price expiration date has gone(in past) and there is no value in Revenue(Null or zero) , then the status will be 'lost'

3)If the price expiration date is in future and there is no value in Revenue, then the status will be 'Open'

The above expression is not giving me the correct results. When I am selecting 'lost' it's also showing me the 'won' records.

When I am selecting 'won', It's giving me the records where there is no value in the 'Revenue'(Zero)

Please Help. Thanks!

Re: Expression Not Working

swuehl — Tue, 29 Sep 2015 16:46:13 GMT

Where are you using the above expression? In the script? In the frontend? In the frontend at different places?

Could you upload a small sample QVW?

Re: Expression Not Working

Kushal_Chawda — Tue, 29 Sep 2015 16:47:04 GMT

Create the flag in script with same expression

If((not isnull(Revenue) and (Revenue)<>0),'Won',if( (IsNull(Revenue) and PriceExpirationDate < Today()),'Lost','Open')) as Status

Now use this field on front end

Re: Expression Not Working

Anonymous — Tue, 29 Sep 2015 16:51:59 GMT

I am using it in the chart Dimension(Calculated Dimension)

Re: Expression Not Working

Anonymous — Tue, 29 Sep 2015 16:53:40 GMT

Revenue and PriceExpirationDate are from two separate tables

Re: Expression Not Working

swuehl — Tue, 29 Sep 2015 16:54:09 GMT

And where does the Status field and list box comes from?

Re: Expression Not Working

swuehl — Tue, 29 Sep 2015 16:55:16 GMT

I would highly recommend that you upload a small sample, so that we can investigate your setting in detail.

Re: Expression Not Working

sergiorey — Tue, 29 Sep 2015 17:51:29 GMT

Hi Chandni,

Is there any relation between these 2 tables?

If yes, join them so as to have both fields in the same table and then create the flag.

Re: Expression Not Working

Tue, 29 Sep 2015 18:02:13 GMT

Mapping PriceExpirationDate field in the table containing the field Revenue, example:

Map_PriceExpDate:

Mapping Load PrimaryKey,

PriceExpirationDate

FROM TablePrice;

TableWhitRevenue:

LOAD *,

If(not isnull(Revenue) and (Revenue)<>0,'Won',

if( (IsNull(Revenue) and APPLYMAP('Map_PriceExpDate',PrimaryKey) < Today()),'Lost','Open')) as Status,

FROM Table2;