topic Re: Date Calculation within Set Analysis in QlikView

Date Calculation within Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Date Calculation within Set Analysis

sunny_talwar — Mon, 02 May 2016 16:39:41 GMT

How about this?

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

Re: Date Calculation within Set Analysis

Anonymous — Tue, 03 May 2016 16:38:53 GMT

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?

Re: Date Calculation within Set Analysis

sunny_talwar — Tue, 03 May 2016 16:51:31 GMT

Is there a sample you are able to share?

Re: Date Calculation within Set Analysis

Anonymous — Wed, 11 May 2016 17:58:11 GMT

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

Thanks