Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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!

1 Solution

Accepted Solutions
Kushal_Chawda

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
swuehl
MVP
MVP

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?

Kushal_Chawda

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Revenue and PriceExpirationDate are from two separate tables

swuehl
MVP
MVP

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

swuehl
MVP
MVP

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

sergiorey
Partner - Creator
Partner - Creator

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
Author

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;