topic Re: Total several expressions in QlikView

Total several expressions

peterderrington — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Total several expressions

lblumenfeld — Wed, 10 Jul 2019 02:11:45 GMT

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

Re: Total several expressions

peterderrington — Thu, 11 Jul 2019 12:00:02 GMT

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)

Re: Total several expressions

marcus_sommer — Thu, 11 Jul 2019 13:27:07 GMT

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

- Marcus

Re: Total several expressions

peterderrington — Thu, 11 Jul 2019 13:40:03 GMT

Named my expression? In where?

Sorry
Thank you

Re: Total several expressions

marcus_sommer — Thu, 11 Jul 2019 13:48:42 GMT

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

- Marcus

Re: Total several expressions

peterderrington — Thu, 11 Jul 2019 14:50:32 GMT

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.

Re: Total several expressions

marcus_sommer — Thu, 11 Jul 2019 14:59:10 GMT

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

Re: Total several expressions

peterderrington — Thu, 11 Jul 2019 15:07:20 GMT

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?

Re: Total several expressions

marcus_sommer — Thu, 11 Jul 2019 15:28:14 GMT

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