different result I got between using "if" clause and set analysis

gzboy008 — Thu, 18 Jul 2013 01:44:09 GMT

Dear Friends,

It makes me a puzzle by this example case why I got different result in Qlikview by using "if" clause versus set analysis.

The first formula is using "if" clause, this is what I want.

sum

(if(transaction_date<=aggr(nodistinct maxstring(agency_contract_date),agent),amount

))

The second formula is using set analysis. The result is beyond my expectation, the result is different from the first formula.

Sum

({< transaction_date = {"=transaction_date<=aggr(nodistinct maxstring(agency_contract_date),agent)"} >} amount

)

Can somebody illustrate to me why the result is different? How can I achieve the same result by using set analysis?

Thanks!

Re: different result I got between using "if" clause and set analysis

Gysbert_Wassenaar — Thu, 18 Jul 2013 06:09:31 GMT

See attached qvw for an explanation.

gzboy008 — Thu, 18 Jul 2013 06:56:52 GMT

I assigned a uniq_id to each and get the correct result.

Learnt a lot. Thanks so much~