topic Using variables in Set analysis in QlikView

Using variables in Set analysis

markgraham123 — Wed, 20 Jul 2016 00:30:49 GMT

Hi All,

I'm trying to use an aggregated variable in set analysis with dimension in set analysis:

vVariable1 = (Sum({<[Yesterday] -= {'-'}>}qc_overpick_lines))

vVariable2 = (Sum({<[Yesterday] -= {'-'}>}qc_mispick_lines))

vVariable3 = (Sum({<[Yesterday] -= {'-'}>}qc_underpick_lines))

In the set analysis, i'm trying to use these variables:

=(Only({<SHIFTNAME= {'A'}>}vVariable1 +vVariable2+vVariable3))

All i'm doing above statement is, using Shift dimension.

But this is not working..

Any help is highly appreciated!

Re: Using variables in Set analysis

Wed, 20 Jul 2016 00:38:13 GMT

Hi Mark, I think you should be using a dollar expansion here. e.g.

$(vVariable1) + $(vVariable2)+ $(vVariable3).

However it won't work anyway because nested aggregation is not allowed here. Insread can you try this?

=Sum(({<SHIFTNAME= {'A'}, ({<[Yesterday] -= {'-'}>} qc_overpick_lines)

Sum(({<SHIFTNAME= {'A'}, ({<[Yesterday] -= {'-'}>} qc_mispick_lines)

Sum(({<SHIFTNAME= {'A'}, ({<[Yesterday] -= {'-'}>} qc_underpick_lines)

You can store each of those expressions in variables and say

=$(v1)+$(v2)+$(v3)

Thanks,

Ram

Re: Using variables in Set analysis

markgraham123 — Wed, 20 Jul 2016 00:42:03 GMT

Ram,

But I have to create too many variables to get to my final layout.

Can we create a single variable which would give us all totals for shift

name A instead of 3 diff. Variables....?

On Tuesday, July 19, 2016, Ramkumar Ramagopalan <qcwebmaster@qlikview.com>

Re: Using variables in Set analysis

Wed, 20 Jul 2016 00:47:09 GMT

Yes Mark. You can put that entire expression into a variable.

Say vShiftNameACalc

=Sum(({<SHIFTNAME= {'A'}, ({<[Yesterday] -= {'-'}>} qc_overpick_lines+ qc_mispick_lines + qc_underpick_lines)

Re: Using variables in Set analysis

markgraham123 — Wed, 20 Jul 2016 01:01:22 GMT

This did not work Ram!

=Sum(({<SHIFTNAME= {'A'}, ({<[Yesterday] -= {'-'}>} qc_overpick_lines+ qc_mispick_lines + qc_underpick_lines)

This worked

=(Sum({<SHIFTNAME= {'A'},[Yesterday] -= {'-'}>}qc_overpick_lines))

(Sum({<SHIFTNAME= {'A'},[Yesterday] -= {'-'}>}qc_mispick_lines))

(Sum({<SHIFTNAME= {'A'},[Yesterday] -= {'-'}>}qc_underpick_lines))

Re: Using variables in Set analysis

Wed, 20 Jul 2016 01:12:39 GMT

I see 2 brackets after Sum. Please remove one.

Sent from my iPhone

Re: Using variables in Set analysis

markgraham123 — Wed, 20 Jul 2016 01:20:54 GMT

Thanks a lot Ram!