Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All,
I am facing an issue between Text box and Straight table in QlikView.
I have a Dimension ProjectID and three expressions as below , and what the data is showing is as
Expression1 value = Expression2 * Expression3
but I am trying to the same in a text object because of not having dimension the aggregation is behaving differently and summing up
the final value. please see below data.
ProjectID | Savings | Price | Quantity |
---|---|---|---|
Total | 5608211.58 | 2629.26 | 2133 |
113 | 2482300 | 2575 | 964 |
113 | 16208.97 | 18.03 | 899 |
114 | 9782.1 | 36.23 | 270 |
The problem when I write "Savings" expression in a text object is I am getting - 5608211.58 which is incorrect as it is calculating
2629.26 * 2133.
The correct value for savings is 2482300+16208.97 + 9782.1 = 2508291.07
The formula used for Savings is below , in that first part is Price , second part is Quantity.
=Sum({<rcv_Flag ={1}, VIP_PROJECT_STATUS={'Complete'},[pol.CANCEL_FLAG]={'N'},[VIP_NEW_ITEM_DETAIL.VIP_NEW_ITEM]={'No'} >}
distinct [VIP_NEW_ITEM_DETAIL.VIP_ITEM_BASELINE_COST] -pol_UNIT_PRICE)
*
Sum({<rcv_Flag={1}, VIP_PROJECT_STATUS={'Complete'},[pol.CANCEL_FLAG]={'N'},[VIP_NEW_ITEM_DETAIL.VIP_NEW_ITEM]={'No'} >}
Aggr(rcv_QUANTITY_Test,rcv_TRANSACTION_DATE,rcv_TRANSACTION_ID))
Any suggestion will be greatful.
This should do it
Sum(Aggr(
Sum({<rcv_Flag ={1}, VIP_PROJECT_STATUS={'Complete'},[pol.CANCEL_FLAG]={'N'},[VIP_NEW_ITEM_DETAIL.VIP_NEW_ITEM]={'No'} >}
distinct [VIP_NEW_ITEM_DETAIL.VIP_ITEM_BASELINE_COST] -pol_UNIT_PRICE)
*
Sum({<rcv_Flag={1}, VIP_PROJECT_STATUS={'Complete'},[pol.CANCEL_FLAG]={'N'},[VIP_NEW_ITEM_DETAIL.VIP_NEW_ITEM]={'No'} >}
Aggr(rcv_QUANTITY_Test,rcv_TRANSACTION_DATE,rcv_TRANSACTION_ID))
, ProjectID))
This should do it
Sum(Aggr(
Sum({<rcv_Flag ={1}, VIP_PROJECT_STATUS={'Complete'},[pol.CANCEL_FLAG]={'N'},[VIP_NEW_ITEM_DETAIL.VIP_NEW_ITEM]={'No'} >}
distinct [VIP_NEW_ITEM_DETAIL.VIP_ITEM_BASELINE_COST] -pol_UNIT_PRICE)
*
Sum({<rcv_Flag={1}, VIP_PROJECT_STATUS={'Complete'},[pol.CANCEL_FLAG]={'N'},[VIP_NEW_ITEM_DETAIL.VIP_NEW_ITEM]={'No'} >}
Aggr(rcv_QUANTITY_Test,rcv_TRANSACTION_DATE,rcv_TRANSACTION_ID))
, ProjectID))
Let me give a try
Thanks alot of sunny