Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
When I am selecting 'won', It's giving me the records where there is no value in the 'Revenue'(Zero)
Please Help. Thanks!
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
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?
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
I am using it in the chart Dimension(Calculated Dimension)
Revenue and PriceExpirationDate are from two separate tables
And where does the Status field and list box comes from?
I would highly recommend that you upload a small sample, so that we can investigate your setting in detail.
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.
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;