topic Total including forced nulls in QlikView

Total including forced nulls

paulm — Thu, 01 Jul 2010 12:37:17 GMT

Hi,

I am quite new to QlikView but think this might be a bug, or it might just be me being stupid.

Anyway I have a pivot table which displays data for every row in a certain column. I only want to show a certain months so if it is not within those months I have replaced the value with a '-'. This is working fine. The issue is that the total (at the top of the column) includes these values that I have replaced with a null value when I select more than one of the conditioning selectors. When I select only one of the selectors the total does not show a value.

Any ideas if this is a bug, or if there is an easy way around?

Thanks in advance.

Paul

Total including forced nulls

Or — Thu, 01 Jul 2010 13:11:53 GMT

It's hard to be sure without seeing the actual formula you used, but I'm guessing you used something along the lines of

If(something=true,sum(Value),null())

When QV evaluates this at the summary level, your condition probably evaluates to true, and thus the values are all summed. If that's the case, try replacing it with something along the lines of:

sum(if(something=true,value))

If you need more assistance with this, please repost and try to include either a sample QVW or sample values and expected results.

Total including forced nulls

Thu, 01 Jul 2010 13:11:54 GMT

Hi Paul,

Did you try by using the set analysis calculation ?

Total including forced nulls

paulm — Thu, 01 Jul 2010 13:48:53 GMT

The code I am using is within the expression tab:


= if(RegisterType='Wattless Units' and sum ({<CancelledBsegFlg={0}>} CI_BSEG_SQ.BILL_SQ)<0,
null(),
 if(RegisterType='Load Factor' or RegisterType='Odd or Even Month',
 null(),
 if(RegisterType='Winter Max Demand' and ConsumptionMonth='Mar',
 '-',
 if(RegisterType='Winter Max Demand' and ConsumptionMonth='Apr',
 '-',
 if(RegisterType='Winter Max Demand' and ConsumptionMonth='May',
 '-',
 if(RegisterType='Winter Max Demand' and ConsumptionMonth='Jun',
 '-',
 if(RegisterType='Winter Max Demand' and ConsumptionMonth='Jul',
 '-',
 if(RegisterType='Winter Max Demand' and ConsumptionMonth='Aug',
 '-',
 if(RegisterType='Winter Max Demand' and ConsumptionMonth='Sep',
 '-',
 if(RegisterType='Winter Max Demand' and ConsumptionMonth='Oct',
 '-',
 sum ({<CancelledBsegFlg={0}>} CI_BSEG_SQ.BILL_SQ),

))))))))))

If I was to use set analysis would I have to write a vlue into the loader?

I added the sum expression and got an error:


= if(RegisterType='Wattless Units' and sum ({<CancelledBsegFlg={0}>} CI_BSEG_SQ.BILL_SQ)<0,
null(),
 if(RegisterType='Load Factor' or RegisterType='Odd or Even Month',
 null(),
 sum(if(RegisterType='Winter Max Demand' and ConsumptionMonth='Mar',
 '-',
 sum(if(RegisterType='Winter Max Demand' and ConsumptionMonth='Apr',
 '-'),
 sum(if(RegisterType='Winter Max Demand' and ConsumptionMonth='May',
 '-'),
 sum(if(RegisterType='Winter Max Demand' and ConsumptionMonth='Jun',
 '-'),
 sum(if(RegisterType='Winter Max Demand' and ConsumptionMonth='Jul',
 '-'),
 sum(if(RegisterType='Winter Max Demand' and ConsumptionMonth='Aug',
 '-'),
 sum(if(RegisterType='Winter Max Demand' and ConsumptionMonth='Sep',
 '-'),
 sum(if(RegisterType='Winter Max Demand' and ConsumptionMonth='Oct',
 '-'),
 sum ({<CancelledBsegFlg={0}>} CI_BSEG_SQ.BILL_SQ),

))))))))))

Thanks,

Paul

Total including forced nulls

Or — Thu, 01 Jul 2010 14:05:00 GMT

Well, that's certainly a long expression. 🙂

When your expression is evaluated at the summary level, it's probably evaluating all your conditions to 'false' - at the summary level, no single condition is true. Therefor, it uses the last Else, which is your sum. This is standard (and correct) behavior. You will need to re-write the formula so that the aggregation is outside the if() blocks rather than inside it, e.g.

sum(

{<CancelledBsegFlg={0}>}

if(Condition1=true,CI_BSEG_SQ.BILL_SQ,if(Condition2=true,CI_BSEG_SQ.BILL_SQ))

)

Alternatively,

sum(

{<CancelledBsegFlg={0}>}

if(Condition1=true,null(),if(Condition2=true,null(),CI_BSEG_SQ.BILL_SQ))

)

This being the same thing but evaluating for "Remove value if condition applies" rather than "Add value if condition applies".

I'm not sure how your formula will work out with this syntax, since it contains summaries inside the actual conditions, and I think QV does not allow aggregations within aggregations.

The thing to keep in mind when using conditional sums is that If your IF..THEN..ELSE block is outside the aggregation, it will be evaluated *once* for each dimension or summary, and then run the summary for the entire data if it evaluates to true. If you want to some partial data, you must place the condition inside the aggregation.

Total including forced nulls

paulm — Thu, 01 Jul 2010 14:46:07 GMT

Worked a treat!

Much appricated as I wasted about half a day! Glad to see it was so simple lol

Thanks!