topic How to use set Analysis to solve expression which associate with dimention? in QlikView

How to use set Analysis to solve expression which associate with dimention?

Wed, 12 Jan 2011 07:19:35 GMT

Variables:

vContractDate =min(ContractDate)

Qustion just like above pic, I want to calc each CustID's asset that DoneDate >=ContractDate

That means the condition associate with dimention

How to solve this ?

How to use set Analysis to solve expression which associate with dimention?

jonathandienst — Wed, 12 Jan 2011 08:14:09 GMT

You can't use a set expression in this case, because set expressions are evaluated on the entire data set, outside the context of the chart and know nothing of the dimensions in the chart. The alternative is sumif.

Something like:

Sum(If(DoneDate>=ContractDate, Asset))

Jonathan

How to use set Analysis to solve expression which associate with dimention?

Wed, 12 Jan 2011 08:41:07 GMT

Thanks your reply

IF using sumif, it would be related with client selections ,for example I choose doneDate:20100108, the result will be all zero.

It also a big problem.

Any other solution?

How to use set Analysis to solve expression which associate with dimention?

jonathandienst — Wed, 12 Jan 2011 08:51:14 GMT

If you want to override user selections, you can combine a set expression with the sumif. The user selection override does not need the chart context, so you can use a set expression for this purpose.

To override the selection of DoneDate use a blank set expression target like:

Sum({<DoneDate=>} If(DoneDate>=ContractDate, Asset))

Override DoneDate and CustID selections:

Sum({<DoneDate=, CustID=>} If(DoneDate>=ContractDate, Asset))

Override all user selections (careful with this one):

Sum({1} If(DoneDate>=ContractDate, Asset))

Jonathan

How to use set Analysis to solve expression which associate with dimention?

Wed, 12 Jan 2011 09:35:35 GMT

Good done!,Thanks

How to use set Analysis to solve expression which associate with dimention?

Wed, 12 Jan 2011 09:36:11 GMT

Good done, Thanks

How to use set Analysis to solve expression which associate with dimention?

Wed, 12 Jan 2011 10:54:15 GMT

Actually you can resolve a set expression at the dimension level with the use of variables and conditional testing.

I recently created a Wiki on the subject which you might find of interest.

Regards,

Gordon