May I know why the ordering in expression of aggr with total can affect the query result?

[gzboy008]

Hi everyone,

May I know why the ordering in expression of aggr with total produces different query result?

This is my expression. When I placed it in different places in expression box of straight table, it's so stange that the query result becomes different. But I found when I placed this expression to be the final one, the result's fine. Hope somebody can explain the reason. Thanks.

aggr(
sum(total<Agent_History_agent_code>{$<Category={'FYCB'},Agent_History_Primary_Agent_Code=e({$<Agent_History_Dummy_Agent={

"1"}>}),
intern_indicator-={'1'},Commission_Date={"<=$(=date#(QVIFinYearThisYearCutOff,'YYYY-MM-DD'))>=$(=date#(QVIFinYearThisYear,'YYYY-MM-DD'))"}>}[commission amt (HKD)]+[10% extra (HKD)])
,primary_district_code,Agent_History_agent_code)

[Gysbert_Wassenaar]

The aggr function is necessary when you nest aggregation functions like sum, avg, count, max, etc. In your expression there are no nested aggregation functions, only one sum. So, the aggr function is probably not needed at all. See QlikView Technical Brief - AGGR.docx and When should the Aggr() function NOT be used?

[gzboy008]

Hi Wassenaar,

Sorry for my late reply. Actually the coding I posted is just a slice.

Below code is what I'm using in my expression.

Why I use aggr function is because in dimension I use district code as dimension, it's a high level concept versus agent level. In expression I need to dive into detail to see every agent's information who is under the district. If the agent meets some criteria, the agent count will be marked as 1. The expression's purpose is to summarize how many agents fulfil the requirement under the district. So I use aggr function. The question is when I put this expression in different place in the expression box in straight table for example promote or demote. The outcome is different. It's quite odd. But the document you provided is useful. Thanks so much!

sum

(if(
if(primary_district_code=aggr(maxstring({$<
Agent_History_Current_Type={'AGT'},
Agent_History_Status={'I'},
Agency_Agent_History_Entry_Pass-={'PASS2'},
intern_indicator-={'1'}
>}
primary_district_code),Agent_History_agent_code),
aggr(
sum(total<Agent_History_agent_code>{$<Category={'FYCB'},Agent_History_Primary_Agent_Code=e({$<Agent_History_Dummy_Agent={"1"}>}),
intern_indicator-={'1'},Commission_Date={"<=$(=date#(QVIFinYearThisYearCutOff,'YYYY-MM-DD'))>=$(=date#(QVIFinYearThisYear,'YYYY-MM-DD'))"}>}[commission amt (HKD)]+[10% extra (HKD)])
,primary_district_code,Agent_History_agent_code),0)
>=
aggr(max(
if(Agency_Agent_History_Entry_Pass='PASS1' and Agent_History_Status='I' and intern_indicator<>'1' and Agent_History_Dummy_Agent<>'1',
if(((mid(QVIFinYearThisYearCutOff,1,4)*12 + mid(QVIFinYearThisYearCutOff,6,2))-
(mid(date(Agent_current_contract_dt,'YYYY-MM-DD'),1,4)*12 + mid(date(Agent_current_contract_dt,'YYYY-MM-DD'),6,2))+ 1)<=3,78000/12*3, //months between agent contract dt and rpt dt<=3, 78000/12*3
if(date(Agent_History_Contract_Date)<date(QVIFinYearThisYear),//agent contract dt<fin year start dt,73000/12* months variance between fin year cut off date and fin year start date
73000/12*((mid(QVIFinYearThisYearCutOff,1,4)*12 + mid(QVIFinYearThisYearCutOff,6,2))-
(mid(date(QVIFinYearThisYear,'YYYY-MM-DD'),1,4)*12 + mid(date(QVIFinYearThisYear,'YYYY-MM-DD'),6,2))+ 1),
if(date(Agent_History_Contract_Date)<makedate(year(QVIFinYearThisYearCutOff),4,1),//agent contract dt<April 1st of fin year, 73000/12*prorata quota
73000/12*((mid(QVIFinYearThisYearCutOff,1,4)*12 + mid(QVIFinYearThisYearCutOff,6,2))-
(mid(date(Agent_current_contract_dt,'YYYY-MM-DD'),1,4)*12 + mid(date(Agent_current_contract_dt,'YYYY-MM-DD'),6,2))+ 1),
78000/12*((mid(QVIFinYearThisYearCutOff,1,4)*12 + mid(QVIFinYearThisYearCutOff,6,2))-//agent contract dt>=April 1st of fin year, 78000/12*prorata quota
(mid(date(Agent_current_contract_dt,'YYYY-MM-DD'),1,4)*12 + mid(date(Agent_current_contract_dt,'YYYY-MM-DD'),6,2))+ 1))))
))
,primary_district_code,Agent_History_agent_code)
,1,0))