12 Replies Latest reply: Jul 7, 2017 7:29 AM by Sébastien Fatoux

Above and below function

Hi,

I have data like below.

I have used Above function but values is coming NULL.

Datesecond DateSalesCalcu
1/26/2016-20002000
2/1/20162/1/2016-
2/13/2016-50005000

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

• Re: Above and below function

Do you have one dimension or multiple dimensions? May be you need to use Above(TOTAL....) and Below(TOTAL...)

• Re: Above and below function

I have 3 dimensions.. Total also is not working

• Re: Above and below function

Would you be able to share a sample?

• Re: Above and below function

No Sunny, That is the sample data which i have shared

• Re: Above and below function

It's work fine with TOTAL

=If((Only(Date) - Only([second Date])) = 0,Below(TOTAL Sum(Sales)), sum(Sales))

= Above(TOTAL Sum(Sales))+((Below(TOTAL Sum(Sales))-Above(TOTAL Sum(Sales)))*(Only([second Date])-Only(Date)))/(Below(TOTAL Only(Date))-Above(TOTAL Only(Date)))

See the application in attachment

• Re: Above and below function

Hi Sebastien,

Thanks for the reply..

Can you attach the application

• Re: Above and below function

The application was attached ???

• Re: Above and below function

Still it is showing null values sebastien

• Re: Above and below function

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 ?

• Re: Above and below function

Hi Sebastien,

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

• Re: Above and below function

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 ...