topic Re: above return NULL on the first dimension in QlikView

above return NULL on the first dimension

Wed, 28 Aug 2013 10:14:00 GMT

I have the following Data

Dimension : 2013 - 2012 - 2011

Expression: = above([Trade Receivables]) - ([Trade Receivables]

Now, when i run the formula and i select the dimensions 2011 - 2012 - 2013, i have the following results

2011 : -

2012 : 5.000

2013 : 7.000

How can I change the formula so that the data for 2011 dissapears ? I would like to select the data 2012 - 2013 in my dimenstion and obtain the same results as above without 2011.

thanks for you help in advance

Re: above return NULL on the first dimension

CELAMBARASAN — Wed, 28 Aug 2013 10:22:26 GMT

If you have DateField then

try this

$(Sum({<Year=, DateField={"<$(=Min(DateField))"}>} [Trade Receivables Expression])) + above([Trade Receivables]) - ([Trade Receivables]

Hope it helps

Celambarasan

Re: above return NULL on the first dimension

Wed, 28 Aug 2013 11:19:41 GMT

i try the following changes to your formula

my dateField is = Year

$(Sum({<Year=, Year={"<$(=Min(Year))"}>} [Trade Receivables])) + above([Trade Receivables]) - [Trade Receivables]

but this is not working. I still have "-" for year 2011

Re: above return NULL on the first dimension

CELAMBARASAN — Wed, 28 Aug 2013 11:28:11 GMT

and then check suppress value is null option in dimension for the year field.

Re: Re: above return NULL on the first dimension

Wed, 28 Aug 2013 11:44:10 GMT

still not working

i have attached the file to that you can see what i did.

the result that i expect is to select 2012 and 2013 and have the figure of trade receivables for both years. Without 2011

Re: above return NULL on the first dimension

CELAMBARASAN — Wed, 28 Aug 2013 12:00:14 GMT

Try with this

=$(Sum({<Year={"<$(=Min(Year))"}>} [Trade Receivables])) + Alt(above([Trade Receivables]),0) - [Trade Receivables]

Re: above return NULL on the first dimension

Wed, 28 Aug 2013 12:09:00 GMT

still not working because when i select year 2012/2013, i have - 5.000 for year 2012 and the right result should be -3.000.

Re: above return NULL on the first dimension

CELAMBARASAN — Wed, 28 Aug 2013 12:14:10 GMT

I missed out the negative

=$(Sum({<Year={"<$(=Min(Year))"}>} [Trade Receivables]) * -1) + alt(above([Trade Receivables]),0) - [Trade Receivables]

Re: above return NULL on the first dimension

Wed, 28 Aug 2013 12:23:59 GMT

still not working

Re: above return NULL on the first dimension

CELAMBARASAN — Wed, 28 Aug 2013 12:30:23 GMT

= Alt(Above(Sum([Trade Receivables])), $(=Sum({<Year={$(=Min(Year)-1)}>} [Trade Receivables]))) - Sum([Trade Receivables])

Re: above return NULL on the first dimension

Wed, 28 Aug 2013 12:35:56 GMT

working. Thanks a lot

Re: above return NULL on the first dimension

fernando_tonial — Wed, 28 Aug 2013 12:36:06 GMT

Hi, you can try this:

RangeSum(above([Trade Receivables])) - Sum([Trade Receivables])

Best Regards

Tonial.