Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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) 
ModelA  10  8  2 
ModelB  60  116  56 
ModelC  775  456  319 
ModelD  40  124  84 
ModelE  52  59  7 
ModelF  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
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
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
sum(aggr(if(sum(Forecast)>sum(BillingQuantity),Sum(Forecast)Sum(BillingQuantity)),Forecast,BillingQuantity))
Please check the qlikview file.
Thanks and Regards, Swarnendu Haldar.
Yeah It works...
Thanks you very much @Vegar . 🙂
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))  
ModelA  10  8  48  0  46  
ModelB  60  116  205  0  261  
ModelC  775  456  1832  0  1513  
ModelD  25  2  2  8  13  
ModelE  52  59  2303  0  2303  
ModelF  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))
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!.