Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
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 NumberForecastBilling QuantitySum(Forecast)-Sum(Billing Qunatity)
Model-A1082
Model-B60116-56
Model-C775456319
Model-D40124-84
Model-E5259-7
Model-F240650-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 (3)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

image.png

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

7 Replies
Highlighted
MVP
MVP

You can put a check before final summation, something like:
Sum(If(yourvalue>0, yourvalue))
Highlighted
Partner
Partner

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

image.png

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Highlighted
Creator
Creator

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

Highlighted
Creator II
Creator II

Please check the qlikview file.

 

 

Thanks and Regards, Swarnendu Haldar.

Highlighted
Contributor III
Contributor III

Yeah It works...

Thanks you very much @Vegar🙂

Highlighted
Contributor III
Contributor III

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-A108480-46 
Model-B601162050-261 
Model-C77545618320-1513 
Model-D2522813 
Model-E525923030-2303 
Model-F801020347 

 

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

 

Highlighted
Contributor III
Contributor III

Friend Please!...

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

JASalinas_0-1588531656092.png

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