topic Re: Aggregation issue in QlikView

Aggregation issue

kkkumar82 — Thu, 16 Nov 2017 14:57:15 GMT

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.

sunny_talwar — Thu, 16 Nov 2017 15:00:58 GMT

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

kkkumar82 — Thu, 16 Nov 2017 15:13:04 GMT

Let me give a try

kkkumar82 — Fri, 17 Nov 2017 13:16:07 GMT

Thanks alot of sunny