topic Re: Above and below function in QlikView

Above and below function

khalander — Thu, 06 Jul 2017 11:36:44 GMT

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

Re: Above and below function

sunny_talwar — Thu, 06 Jul 2017 11:40:21 GMT

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

Re: Above and below function

khalander — Thu, 06 Jul 2017 13:10:11 GMT

I have 3 dimensions.. Total also is not working

Re: Above and below function

sunny_talwar — Thu, 06 Jul 2017 13:12:29 GMT

Would you be able to share a sample?

Re: Above and below function

khalander — Thu, 06 Jul 2017 13:18:35 GMT

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

Re: Above and below function

sunny_talwar — Thu, 06 Jul 2017 13:19:57 GMT

okay

Re: Above and below function

sfatoux72 — Thu, 06 Jul 2017 20:45:52 GMT

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

khalander — Fri, 07 Jul 2017 04:39:34 GMT

Hi Sebastien,

Thanks for the reply..

Can you attach the application

Re: Above and below function

khalander — Fri, 07 Jul 2017 05:21:18 GMT

Still it is showing null values sebastien

Re: Above and below function

sfatoux72 — Fri, 07 Jul 2017 08:26:52 GMT

The application was attached ???

Re: Above and below function

sfatoux72 — Fri, 07 Jul 2017 08:28:53 GMT

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

khalander — Fri, 07 Jul 2017 09:36:59 GMT

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

sfatoux72 — Fri, 07 Jul 2017 11:29:10 GMT

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