Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Set Analysis Not Working

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

3 Replies
Not applicable

Re: Set Analysis Not Working

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.

1.

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

2.

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.

Not applicable

Re: Set Analysis Not Working

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

Not applicable

Re: Set Analysis Not Working

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