topic Set Analysis Not Working in QlikView

Set Analysis Not Working

Wed, 04 Jan 2012 19:18:37 GMT

Dear All,

I am calculating rate using a complex Set Analysis in straight table.

It is working but I notice that at the end of table there is no total for this column.

Yes, I find out the region of this, that is that "I am not using any aggregate function before this complex set analysis".

As soon as I am using sum or avg before this I am getting null error.

Here I am help less, I don't know how to solve it.

Please help.

I am attaching my sample QVW here

Thanks & Regards

- anil

Re: Set Analysis Not Working

Wed, 04 Jan 2012 23:29:07 GMT

Hello anil,

I analyzed your expressions and I found (at least) two reasons, why you have dashes (undefined values) in your expressions. And as a conseqence in the according totals.

sometimes you are dividing by zero --> expression result is undefined, this is a '-'

Example see third expression in my copied chart --> your divisor is 0

In some cases your ifs are both "false" and returning a null-value

Example see forth expression in my chart. For testing purpose I used a '-1' as default value

HtH

Roland

Note:

I didn't miss any aggr()-functions like you said in your post. Look at the reasons I mentioned above.

Re: Set Analysis Not Working

Wed, 04 Jan 2012 23:44:30 GMT

Roland Sir,

Very good morning, now it is morning in India, Thanks for your reply.

I have Solved this problem, by changing my Set Analysis writing approach. Now I am writing This

(

SUM( {$ < trnType={'Sales'}, docDate={">=$(vlst1FyDate1) <=$(vlst1FyDate2)"}, [Financial Year]= > } (BasicAmt*iExchangeRate) ) -

SUM( {$ < trnType={'Sales'}, fabType={'Synthetic'}, docDate={">=$(vlst1FyDate1) <=$(vlst1FyDate2)"}, [Financial Year]= > } (stdPackAmt-PackAmt) )

) -

SUM( {$ < trnType={'CRN','SALRET','PROCRN'}, crnType={'SP'}, docDate={">=$(vlst1FyDate1) <=$(vlst1FyDate2)"}, [Financial Year]= > } invGrTotal )

) / sum( {$ < trnType={'Sales'}, docDate={">=$(vlst1FyDate1) <=$(vlst1FyDate2)"}, [Financial Year]= > } Sqmtr )

But I will always consider all the points you suggested here in your mail.

Thanks a lot

Regards.

-anil

Re: Set Analysis Not Working

Thu, 05 Jan 2012 10:48:49 GMT

Hello anil,

glad to help you; or let's say: to assist you, even around half the world.

BtW: When I wrote my post above it was half past midnight in Germany.

Best regards,

Roland