Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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