Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis with dates - how to include a year() on the actual test date

Hi Everyone,

I'm sure this is an easy question for someone to answer, but I'm pretty new and can't seem to find the answer.

I have a date R_SALE_START_DATE and I want to evaluate whether the YEAR of this date is >= a variable called $(TenYear).

So set analysis, I need to tell the following statement not to evaluate R_SALE_START_DATE which is a full date, but to evaluate =year(R_SALE_START_DATE):

=sum( {<R_SALE_START_DATE={"<=$(TenYear)"} variable1)

What is the correct syntax to do this?

Thanks

GPC

2 Replies
fosuzuki
Partner - Specialist III
Partner - Specialist III

Hi RPC,

I think that in the set analysis you cannot use expressions like year(R_SALE_START_DATE) to define the field you want to modify (for example =sum( {<year(R_SALE_START_DATE) ={"<=$(TenYear)"} variable1).

So if I am correct, you could create a field in your load script (I'd recommend to create it in a Time dimension):

year(R_SALE_START_DATE) as Sale_Start_Year

and use it in the set analysis:

=sum( {<Sale_Start_Year={"<=$(TenYear)"} variable1)

Hope this solves your problem.

Regards,

Fernando



rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You could invert the problem and make TenYear a date value in the test like this:

=sum( {<R_SALE_START_DATE={"<=$(=makedate($(TenYear),12,31))"}>} variable1)