Using Multiple variables in a Set Analysis

[#1]

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)

[stigchel · Accepted solution]

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')

[#3]

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 '='.

[stigchel]

You're welcome Mark,

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

[#5]

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