Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
chandni90
Contributor

Expression Not Working

Hi

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.

as.png

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

as.png

Please Help. Thanks!

Tags (1)
1 Solution

Accepted Solutions

Re: Expression Not Working

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

View solution in original post

8 Replies
MVP
MVP

Re: Expression Not Working

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

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

View solution in original post

chandni90
Contributor

Re: Expression Not Working

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

chandni90
Contributor

Re: Expression Not Working

Revenue and PriceExpirationDate are from two separate tables

MVP
MVP

Re: Expression Not Working

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

MVP
MVP

Re: Expression Not Working

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

Partner
Partner

Re: Expression Not Working

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.

Not applicable

Re: Expression Not Working

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;