Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for
Search instead for
Did you mean:
Contributor III

## Get Positive Value in KPI

Hi, I am facing a problem to fetch only the Positive Value from the Difference

The Pivot table is this

 Model Number Forecast Billing Quantity Sum(Forecast)-Sum(Billing Qunatity) Model-A 10 8 2 Model-B 60 116 -56 Model-C 775 456 319 Model-D 40 124 -84 Model-E 52 59 -7 Model-F 240 650 -410

I want to Show the value "Sum(Forecast)-Sum(Billing Qunatity)" in a KPI but only the sum of positive value but unable to do do the KPI gives : 2+(-56)+319+(-84)+(-7) +(-410) =  -236. The Output I want is should be ( 2+319 = 321) i.e only the positive value.

Can anyone help me with that how can i achieve this Output in KPI

Thanks

Rishabh

Labels (2)

• ### Qlik Sense

1 Solution

Accepted Solutions
MVP

You can use SET analysis to accomplish this. I've used your data in an QVF for presentation below.

Try this expression:

``````=Sum({<[Model Number]= {"=sum(Forecast)>=sum([Billing Quantity])"} >}Forecast)
-
Sum({<[Model Number]= {"=sum(Forecast)>=sum([Billing Quantity])"} >}[Billing Quantity])``````

See attached qvf

7 Replies
MVP
You can put a check before final summation, something like:
Sum(If(yourvalue>0, yourvalue))
MVP

You can use SET analysis to accomplish this. I've used your data in an QVF for presentation below.

Try this expression:

``````=Sum({<[Model Number]= {"=sum(Forecast)>=sum([Billing Quantity])"} >}Forecast)
-
Sum({<[Model Number]= {"=sum(Forecast)>=sum([Billing Quantity])"} >}[Billing Quantity])``````

See attached qvf

Creator

sum(aggr(if(sum(Forecast)>sum(BillingQuantity),Sum(Forecast)-Sum(BillingQuantity)),Forecast,BillingQuantity))

Creator II

Please check the qlikview file.

Thanks and Regards, Swarnendu Haldar.

Contributor III
Author

Yeah It works...

Thanks you very much @Vegar🙂

Contributor III
Author

Is this also Possible to get positive value only of this expression

 Model Number Forecast Billing Quantity Forecast_2 Left Quantity (Sum(Forecast) - sum(Billing Quantity)) - (sum(Forecast_2)+sum(Left_Quantity)) Model-A 10 8 48 0 -46 Model-B 60 116 205 0 -261 Model-C 775 456 1832 0 -1513 Model-D 25 2 2 8 13 Model-E 52 59 2303 0 -2303 Model-F 80 10 20 3 47

Can we also get positive value of  following expression :  i.e. 13+47 = 60

(Sum(Forecast) - sum(Billing Quantity)) - (sum(Forecast_2)+sum(Left_Quantity))

Creator

Friend Please!...

I have a KPI From an Expression: Sum({<[Account]={"138*"}> - <[FUND]={"20*"}>}(Amount))  on a Table show me that:

But I want only the Sum of the Amount Positive or Negative after this Sum Positive: 109,395.52 or Negative (-792.78) + (-22,547.98) = -23,440.76

Thank you!. I am a beginner in Qlik Sense.

Thank you Again!.