Date Calculation within Set Analysis

[Anonymous]

Hello,

I am new to Qlikview and have been trying to get to my objective through a series of steps.  I am trying to get a survey count from 2 weeks prior to the current date.  I hard coded values and I get an accurate result.

This Works:

(1)      =sum({$<WeekNum={"$(=17-2)"}>}[SurveyResp])

This Works:

(2)       =Week(Today())-2

For some reason substituting (2) into (1) for the "17" into the Set Analysis statement gives me problems.

Doesn't Work:

=sum({$<WeekNum={"$(=Week(Today())-2)"}>}[SurveyResp])

I have been trying for a long time now and have had no success. I assume functions can't be stated the same for them to evaluate in Set Analysis?

[sunny_talwar]

How about this?

=Sum({$<WeekNum={"$(=Num#(Week(Today())-2))"}>}[SurveyResp])

[Anonymous]

Hi Sunny T,

Thank you for your quick response.  At one time I tried using Num# function as a wrapper but I kept getting a value of 0. So there is still something tricky to transform (= ## -2) is quite tricky to transform even though standalone the function returns the correct value for ##.  This appears to be more than a return datatype issue. I haven't tried it but maybe I need to set the week calc into a variable first then substitute it into the set analysis statement. Thoughts?

[sunny_talwar]

Is there a sample you are able to share?

[Anonymous]

Sorry. I've been out of action for awhile.  I will try create some dummy data in Excel as soon as I can.

Thanks