Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Please Help in Set Analysis Expression
IF(Sum(OrderQty) - Sum(BillQty) > 0,
Sum(OrderQty))
How Can Write This Expression in Set Analysis.
As I said to your earlier question
Set analysis is a filter, not a conditional, so you cannot implement if(...sum()) type expressions using set analysis.
Assuming thatyou mean SumIf()) and that the quantities apply to some sort of order ID, I would create a flag to indicate when the order qty exceeds the bill qty. Enter code like this after loading your main fact table:
...
Join (Fact)
LOAD ID,
IF(Sum(OrderQty) - Sum(BillQty) > 0, 1, 0) as BillFlag
Resident Fact
Group By ID;
...
Adjust table and field names to match your data model.
Now use
Sum({<BillFlag = {1}>} OrderQty)
As I said to your earlier question
Set analysis is a filter, not a conditional, so you cannot implement if(...sum()) type expressions using set analysis.
Assuming thatyou mean SumIf()) and that the quantities apply to some sort of order ID, I would create a flag to indicate when the order qty exceeds the bill qty. Enter code like this after loading your main fact table:
...
Join (Fact)
LOAD ID,
IF(Sum(OrderQty) - Sum(BillQty) > 0, 1, 0) as BillFlag
Resident Fact
Group By ID;
...
Adjust table and field names to match your data model.
Now use
Sum({<BillFlag = {1}>} OrderQty)