Looking for correct syntax to use variable in set analysis

[#1]

Greetings,

I need some help finding correct syntax to use variable in set analysis.

When my expression is written as following, it works fine and returns correct value. Please note that week name "2013/33" is entered in manually.

=Num(Avg({$<[Year Week]={'2013/33'}>}Cost]),'#,##0.00')

I assigned variable to variable to find out present week's weekname and name is as "vPresentWeekName".

vPresentWeekName = Weekname(Today())

I now would like to replace "2013/33" to this new variable "vPresentWeekName" but I could not figure it out.

Following is what I tried but it does not work

Avg({<[Year Week]={$(=vPresentWeekname)}>} Cost)

Would you have any suggestions or recommendations for me? Will be much appreciated.

Best wishes,

p1907

[alexandros17]

Avg({[Year Week]={'$(=vPresentWeekname)'}>} Cost)

add ' symbol ... but are you sure that Weekname(Today()) returns the value formatted as you need?

Hope it helps

[#3]

Hi Alessandro,

Thanks for your swift support and help; really appreciated.

Noted on your expression suggestion but it still returns nothing.

My variable vPresentWeekName = Weekname(Today()) returns 2013/34 for today; which is correct.

What I cannot figure out is that when I enter in the week name manually into the expression, it is working fine. Took your advice to see if variable is returning the value as intended formatting and it seems to.

Where else shall I check or what am I missing, any ideas?

By the way, I added "<" to the expression: Avg({<[Year Week]={$(=vPresentWeekname)}>} Cost)

Best wishes,

p1907

[#4]

try with below syntax

=Avg({<[Year Week]={"$(=$(vPresentWeekname))"}>} Cost)

[alexandros17 · Accepted solution]

Define in the script:

LET vPresentWeekName = Weekname(Today())

then you set analyis expression will be:

=Num(Avg({$ <[Year Week]={$vPresentWeekName}>}Cost]),'#,##0.00')

[#6]

Thank you so much, it worked!