Total several expressions

[peterderrington]

Hi, 

I have several expressions that show a compliance ratio (shown in percentages) and i would like to be able to show a total of all these ratios together (again as a percentage). 

I can't for the life of me work out how to generate this.

Any ideas?

Thanks.

[lblumenfeld]

Can you post an example of what you have for the expressions?

[peterderrington]

Certainly;

I have 6 different expressions which calculate a percentage of compliance for different locations like this:

Count({<Ward={'NSEC03'},[Questions Complete]={'Yes'},[Ward transferred to]-={'NSEC07'}>}Ward)/Count({<Ward={'NSEC03'}, [Ward transferred to]-={'NSEC07'}>}Ward)

Count({<Ward={'NSEC04'},[Questions Complete]={'Yes'},[Ward transferred to]-={'NSEC07'}>}Ward)/Count({<Ward={'NSEC04'}, [Ward transferred to]-={'NSEC07'}>}Ward)

Count({<Ward={'NSEC09'},[Questions Complete]={'Yes'},[Ward transferred to]-={'NSEC07','NSECFAS'}>}Ward)/Count({<Ward={'NSEC09'}, [Ward transferred to]-={'NSEC07','NSECFAS'}>}Ward)

Count({<Ward={'NSEC10','NSEC10C'},[Questions Complete]={'Yes'},[Ward transferred to]-={'NSEC10','NSEC10C'}>}Ward)/Count({<Ward={'NSEC10','NSEC10C'}, [Ward transferred to]-={'NSEC10','NSEC10C'}>}Ward)

Count({<Ward={'NSEC12','NSEC12R'},[Questions Complete]={'Yes'},[Ward transferred to]-={'NSEC12','NSEC12R','NSEC07'}>}Ward)/Count({<Ward={'NSEC12','NSEC12R'}, [Ward transferred to]-={'NSEC12','NSEC12R','NSEC07'}>}Ward)

Count({<Ward={'NSECFAS'},[Questions Complete]={'Yes'},[Ward transferred to]-={'NSEC07','NSEC09'}>}Ward)/Count({<Ward={'NSECFAS'}, [Ward transferred to]-={'NSEC07','NSEC09'}>}Ward)

and then i'm currently using this expression to calculate a total however it is incorrect as it doesn't adjust for the possibilities of inter transfer between those locations:

Count({<Ward={'NSEC03','NSEC04','NSEC09','NSEC10','NSEC10C','NSEC12','NSEC12R','NSECFAS'},[Questions Complete]={'Yes'},[Ward transferred to]-={'NSEC07''NSEC03','NSEC04','NSEC09','NSEC10','NSEC10C','NSEC12','NSEC12R','NSECFAS'}>}Ward)/Count({<Ward={'NSEC03','NSEC04','NSEC09','NSEC10','NSEC10C','NSEC12','NSEC12R','NSECFAS'},[Ward transferred to]-={'NSEC07''NSEC03','NSEC04','NSEC09','NSEC10','NSEC10C','NSEC12','NSEC12R','NSECFAS'}>}Ward)

[marcus_sommer]

Assumingly that you named your expressions with A, B, ... you could try the following: rangeavg(A, B, ...).

- Marcus

[peterderrington]

Named my expression? In where?

Sorry
Thank you

[marcus_sommer]

I meant the label for the expression on which you could refer in another expressions.

- Marcus

[peterderrington]

Sorry, i wasn't in front of my PC when i replied before (probably obvious).

I've added the expression:

RangeAvg('Ward 3 - Compliance Ratio','Ward 4 - Compliance Ratio','Ward 9 - Compliance Ratio','Ward 10 - Compliance Ratio','Ward 12 - Compliance Ratio','FAS - Compliance Ratio')

however nothing is showing up on the graph.

Any ideas?

Thanks.

[marcus_sommer · Accepted solution]

They must be handled like fields and not as strings. Therefore change it into:

RangeAvg([Ward 3 - Compliance Ratio],[Ward 4 - Compliance Ratio],[Ward 9 - Compliance Ratio],[Ward 10 - Compliance Ratio],[Ward 12 - Compliance Ratio],[FAS - Compliance Ratio])

- Marcus

[peterderrington]

Fantastic, thank you so much.

I had a little trouble at first as the original expressions were not enabled (as i didn't want to see them on the graph) but then i realised i could enable them but make them invisible.

The problem of them being in 'xxx' rather than [xxx] was that was what QlikView itself inserted them as and i didnt realise. 

Can i just check - what type of Average does the RangeAvg return?

[marcus_sommer · Accepted solution]

In general are all non-numeric values treated as zero within the rangefunctions but I think there are one or two exceptions because in some cases are non-numerics or NULL ignored instead of be treated as zero. I believe that's the case by rangeavg(). Best would be you check it - just comment one of your expressions and use instead = 0 respectively = null() and watch the changes within your total-expression.

- Marcus