topic Re: Make 0 lowest amount in QlikView

Make 0 lowest amount

Anonymous — Mon, 13 Feb 2017 15:03:44 GMT

The below Straight Table is calculating several thing(see below).

I am looking for either the POINTS DEDUCTED or CATEGORY SCORE to go no lower than 0 or 0%.

Step

1) Count each CATEGORY for EACH SLA

2) Calculate POINTS DEDUCTED

3) Calculate CATEGORY SCORE

Re: Make 0 lowest amount

annafuksa1 — Mon, 13 Feb 2017 15:13:18 GMT

use in your formula formula

=if(expression<0,0 expression)

example

expression = count(POINTS DEDUCTED)

=if(count(POINTS DEDUCTED)<0,0 count(POINTS DEDUCTED))

if want to show only positive values (so if you have -1 show as 1) use function for absolute value -'Fabs'

Anna

Re: Make 0 lowest amount

vishsaggi — Mon, 13 Feb 2017 15:29:57 GMT

May be this?

For each category

= Count(Distinct {< [SLA Impact] = {'High'} >} ID) > 0

= Count(Distinct {< [SLA Impact] = {'Low'} >} ID) > 0

......

Let us know if that works?

Re: Make 0 lowest amount

aarkay29 — Mon, 13 Feb 2017 15:58:27 GMT

Hi Dan,

If(100-[Points Deducted]>0,100-[Points Deducted]) as expression for Category Score.

Similarly for Points deducted too

[=IF(

[Close Period]>'2016 - 01',

If(Critical *(10) + (High*(5))+ (Medium *(3)) + (Low *(1))>0,Critical *(10) + (High*(5))+ (Medium *(3)) + (Low *(1))),

If(Critical *(10) + (High*(5))+ (Medium *(3)) + (Low *(0))>0,Critical *(10) + (High*(5))+ (Medium *(3)) + (Low *(0))>0)

)

Re: Make 0 lowest amount

Anonymous — Mon, 13 Feb 2017 15:59:25 GMT

Anna & Vishwaqrath,

These do not give me what I need- not sure it is even possible.

In the Table included, the TIMELINES POINTS DEDUCTED is -69. Even thou the actual calculation is -69, I want it to show 0 if the number is a negative number.

Dan

Re: Make 0 lowest amount

annafuksa1 — Mon, 13 Feb 2017 16:04:32 GMT

can you post the app or the full formula ?

Re: Make 0 lowest amount

annafuksa1 — Mon, 13 Feb 2017 16:05:34 GMT

My formula should do the trick

Re: Make 0 lowest amount

vishsaggi — Mon, 13 Feb 2017 16:19:19 GMT

May be try this?

= IF(100 - [Points Deducted] < 0, 0, 100- [Points Deducted])

Re: Make 0 lowest amount

aarkay29 — Mon, 13 Feb 2017 16:23:21 GMT

If(100-[Points Deducted]>0,100-[Points Deducted],0) as expression for Category Score. Use this if you want to show 0 if negative

Re: Make 0 lowest amount

Anonymous — Mon, 13 Feb 2017 16:47:44 GMT

rangemax(yourexpression,0)

Re: Make 0 lowest amount

Anonymous — Mon, 13 Feb 2017 17:04:06 GMT

Anna, Aar & Robin,

All of your expressions worked!

Thank You!!!

Dan

Re: Make 0 lowest amount

sunny_talwar — Mon, 13 Feb 2017 17:13:32 GMT

A slightly more efficient apporach (as pointed out by roharoha‌) would be to use RangeMax() here

RangeMax((100 - [Points Deducted]), 0)