Hi,
I have created a DB for daily production of articles against orders. I am stuck in a query. I get the following fields in query for the daily production report.
1. Total order
2. Production Till Date
3. Production Today
4. Total Production
5. Balance Production
Now there are times when the Total Production exceeds the Total Order and in this case the Balance Production shows as negative value. I use the query in Calc in Pivot table and in the row total at the end, it adds all the negative values too, thus giving a variation in Balance Production. The Balance production is being calculated by (sum(TotalOrder) - sum(TotalProduction)).
I would want to use a function like sumif, the above calculation with the option to give the result if it is positive only, negative result it should give the result as zero value.
Thanks,
Amit
[Solved] Sumif type function to use only positive numbers
-
- Posts: 44
- Joined: Mon Aug 27, 2012 12:23 pm
[Solved] Sumif type function to use only positive numbers
Last edited by amitmahajan74 on Fri Apr 01, 2016 10:38 am, edited 1 time in total.
Open Office 4.1.2
Win 10 Proff
Win 10 Proff
Re: Using Sumif type function to use only positive numbers
download/file.php?id=24572 (simple inventory)
Simple SUMIF in SQL:
Simple SUMIF in SQL:
Code: Select all
SELECT SUM("Values") AS FROM "Something" WHERE "Values">0
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 44
- Joined: Mon Aug 27, 2012 12:23 pm
Re: Using Sumif type function to use only positive numbers
Thanks Villeroy, I tried using it but now it's giving the results for values where sum is >0 but eliminating the results where it is less than zero. I am attaching the complete query here, please advise ..
Code: Select all
SELECT "Customer"."Customer", "OrdersLine"."Order No ID", "Order"."Season", "Category"."Category", "Order"."Facility", "Order"."Status", "Order"."Order No", "Product"."Product Name", "Colour"."Colour", "Size"."Size", ( COALESCE ( "OR"."OrderQty", 0.0 ) * "Order"."Rate" ) AS "OrderValue", COALESCE ( "OR"."OrderQty", 0.0 ) AS "Order", COALESCE ( "PB"."ProdQtyBefore", 0.0 ) AS "PrTilDat", COALESCE ( "PTdy"."ProdQtyToday", 0.0 ) AS "PrToday", COALESCE ( "PTotal"."ProdQtyTotal", 0.0 ) AS "ProdTotal", COALESCE ( "OR"."OrderQty", 0.0 ) - COALESCE ( "PTotal"."ProdQtyTotal", 0.0 ) AS "ProdBal" FROM "OrdersLine" LEFT OUTER JOIN ( SELECT "Order No ID", "Size ID", SUM( "OrdQty" ) AS "OrderQty" FROM "OrdersLine" GROUP BY "Order No ID", "Size ID" ) AS "OR" ON "OrdersLine"."Order No ID" = "OR"."Order No ID" AND "OrdersLine"."Size ID" = "OR"."Size ID" LEFT OUTER JOIN ( SELECT "Order No ID", "Size ID", SUM( "ProdQty" ) AS "ProdQtyBefore" FROM "OrdersLine" WHERE "ProdDate" < :RpDt GROUP BY "Order No ID", "Size ID" ) AS "PB" ON "OrdersLine"."Order No ID" = "PB"."Order No ID" AND "OrdersLine"."Size ID" = "PB"."Size ID" LEFT OUTER JOIN ( SELECT "OrdersLine"."Order No ID", "OrdersLine"."Size ID", "Department"."Deptartment", SUM( "OrdersLine"."ProdQty" ) AS "ProdQtyToday" FROM "OrdersLine", "Department" WHERE "OrdersLine"."ProdDate" = :RpDt AND "OrdersLine"."Dept ID" = "Department"."ID" GROUP BY "OrdersLine"."Order No ID", "OrdersLine"."Size ID", "Department"."Deptartment" ) AS "PTdy" ON "OrdersLine"."Order No ID" = "PTdy"."Order No ID" AND "OrdersLine"."Size ID" = "PTdy"."Size ID" LEFT OUTER JOIN ( SELECT "Order No ID", "Size ID", SUM( "ProdQty" ) AS "ProdQtyTotal" FROM "OrdersLine" WHERE "ProdDate" <= :RpDt GROUP BY "Order No ID", "Size ID" ) AS "PTotal" ON "OrdersLine"."Order No ID" = "PTotal"."Order No ID" AND "OrdersLine"."Size ID" = "PTotal"."Size ID" INNER JOIN "Order" ON "OrdersLine"."Order No ID" = "Order"."Order ID" INNER JOIN "Product" ON "Order"."Product ID" = "Product"."ID Number" INNER JOIN "Colour" ON "Order"."Colour ID" = "Colour"."ID Number" INNER JOIN "Size" ON "OrdersLine"."Size ID" = "Size"."ID Number" INNER JOIN "Customer" ON "Product"."Cust ID" = "Customer"."ID" INNER JOIN "Category" ON "Product"."Category ID" = "Category"."ID Number" INNER JOIN "Plan" ON "Order"."Order ID" = "Plan"."OrderNoID"
WHERE COALESCE ( "OR"."OrderQty", 0.0 ) - COALESCE ( "PTotal"."ProdQtyTotal", 0.0 ) > 0
and MONTH( "Plan"."Plan Month" ) = :mth AND YEAR( "Plan"."Plan Month" ) = :yr GROUP BY "OrdersLine"."Order No ID", "Order"."Season", "Order"."Facility", "Order"."Status", "Order"."Order No", "Category"."Category", "Product"."Product Name", "Colour"."Colour", "Size"."Size", "OR"."OrderQty", "PB"."ProdQtyBefore", "PTdy"."ProdQtyToday", "PTotal"."ProdQtyTotal", "Customer"."Customer", "Order"."Rate" ORDER BY "Order"."Order No", "Product"."Product Name", "Colour"."Colour", "Size"."Size"
Open Office 4.1.2
Win 10 Proff
Win 10 Proff
-
- Posts: 44
- Joined: Mon Aug 27, 2012 12:23 pm
Re: Using Sumif type function to use only positive numbers
Got it, used casewhen in the query to use query value >0 else 0. Working now.
Thanks
Thanks
Open Office 4.1.2
Win 10 Proff
Win 10 Proff