topic Re: Using Multiple variables in a Set Analysis in QlikView

Using Multiple variables in a Set Analysis

Tue, 20 Sep 2016 02:02:51 GMT

I am comparing an avg value over a period, to the period prior to it to determine if the change is +/-.

Now I would like to substitute the defined period with variables. Unfortunately I am having no luck.

Current expression:

=if(avg({$<CDIMonthID={">$(=max(CDIMonthID)- 6) ,<=$(=max(CDIMonthID))"}>}CDI)

avg({$<CDIMonthID={">$(=max(CDIMonthID)-12) ,<=$(=max(CDIMonthID)-6)"}>}CDI),

'qmem://<bundled>/BuiltIn/arrow_n_r.png',

'qmem://<bundled>/BuiltIn/arrow_s_g.png')

Proposed expression (which is not working)

=if(avg({$<CDIMonthID={">$(=max(CDIMonthID)- $(vCDIPeriod)) ,<=$(=max(CDIMonthID))"}>}CDI)

avg({$<CDIMonthID={">$(=max(CDIMonthID)-($(vCDIPeriod)*'2')) ,<=$(=max(CDIMonthID)-$(vCDIPeriod))"}>}CDI),

'qmem://<bundled>/BuiltIn/arrow_n_r.png',

'qmem://<bundled>/BuiltIn/arrow_s_g.png')

I have tried different syntax and values to no avail.

(My first question posted on this forum, after finding the answers to my previous questions in other posts)

Re: Using Multiple variables in a Set Analysis

stigchel — Tue, 20 Sep 2016 16:04:47 GMT

Can you try with this:

=if(avg({$<CDIMonthID={">$(=max(CDIMonthID)- $(vCDIPeriod)) ,<=$(=max(CDIMonthID))"}>}CDI)

avg({$<CDIMonthID={">$(=max(CDIMonthID)-($(=vCDIPeriod*2))) ,<=$(=max(CDIMonthID)-$(vCDIPeriod))"}>}CDI),

'qmem://<bundled>/BuiltIn/arrow_n_r.png',

'qmem://<bundled>/BuiltIn/arrow_s_g.png')

Re: Using Multiple variables in a Set Analysis

Tue, 20 Sep 2016 23:36:20 GMT

Thanks Piet.

I knew it would be a matter of simple order and syntax, but I couldn't see the forest for the the trees. Looking at the solution above makes sense to me now. Since I'm formulating an equation, I need to define it as such with the '='.

Re: Using Multiple variables in a Set Analysis

stigchel — Wed, 21 Sep 2016 08:57:22 GMT

You're welcome Mark,

Any Dutch roots? "Door de bomen het bos niet zien" is not uncommon for me as well.

Re: Using Multiple variables in a Set Analysis

Wed, 21 Sep 2016 23:06:20 GMT

Hi Piet

Yes, I’m South African with some Dutch heritage along the line.

It’s always the simpler ones that catch me out ☺.

Thanks again.

Regards

Mark van der Berg