Above and below function

[khalander]

Hi,

I have data like below.

I have used Above function but values is coming NULL.

Date	second Date	Sales	Calcu
1/26/2016	-	2000	2000
2/1/2016	2/1/2016	-
2/13/2016	-	5000	5000

wherever date is equal to second date i want to apply below condition and i want to show that in calcu.

above(sales)+((Below(sales)-Above(sales))*(secondate-date))/(Below(date)-Above(date))

but values is coming nulls for the above calculation

Please do the needful

[sfatoux72]

You see Null value in the application that I sent you ?

Or you use the expression in your application?

Could you send me the application with semple data where yoou see Null ?

[khalander]

Hi Sebastien,

I am getting null values in my application. This application in our server. i am not able to share.

[sfatoux72]

The problem is certainly due to the date field that are not identified as dates.

To test, use this expression (= Year (Date)) in a Text object and select a date. If you have a value your field is considered a date, otherwise it is considered as text.


Try this with your loading script to :

LOAD
    Date(Date#(Date, 'MM/DD/YYYY')) as Date,
    Date(Date#([second Date])) as [second Date],
    Sales,
    Calcu
FROM ...