topic Ignore first value using Above() function in QlikView

Ignore first value using Above() function

Wed, 31 Jul 2013 18:38:49 GMT

Is there a way to ignore the first delta value when using the Above() function? I'm plotting the difference in # of users in a system between months, and the first calculation is far above the rest because it's subtracting 0 in the first month (no data for Min(month) - 1). Is there a way to force the first value to be zero?

In the example below I turned the difference into a percentage, but the idea remains:

Re: Ignore first value using Above() function

swuehl — Wed, 31 Jul 2013 20:59:40 GMT

Maybe like

=if(rowno() = 1,

above(YourExpression)

)

Re: Ignore first value using Above() function

Wed, 31 Jul 2013 21:23:54 GMT

Good thought, however unfortunately this doesn't work. I tried if(RowNo() = 1..) and if(RowNo() = 2...), but neither produced the desired result. It's not placing a zero in the first position the way I would like (RowNo() = 1 didn't change the plot at all - I assume since when I take the difference of (value - PreviousVaue), the calcuation cannot work until the second data point.

Re: Ignore first value using Above() function

Thu, 01 Aug 2013 02:31:31 GMT

I personnaly use a len() function to ignore the first value or put it at 1. You can put whatever you want in else part.

=if(len(Above([Amount Gross Margin]))>0,avg(SaleGrossMargin),'')

Re: Ignore first value using Above() function

Thu, 01 Aug 2013 04:30:56 GMT

try this

=if(rowno() = 1,

' ',

above(YourExpression)

)

note--if you only use one expression then first row automatically supressed because it take as a null value.

hope this helps you.

Regards

Re: Ignore first value using Above() function

swuehl — Thu, 01 Aug 2013 20:21:54 GMT

It would be helpful if you could post the complete expression, or even better a small sample app.

above(YourExpression) should return NULL in the first row, not zero, so subtracting above(YourExpression) from YourExpression, like

=sum(UsageCount) - above(sum(UsageCount))

should also be NULL, not equal to sum(UsageCount).