Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
egemenekinci
New Contributor III

Filtering with Dates in Set Analysis - Not working

Dear experts,

I am trying to filter data with sum function in expression and somehow it is not working. I have searched the same topics and tried to implement solutions which were suggested before but no luck.

I have a variable a like that : SET vCalcDate=Date('04/30/2016','MM/DD/YYYY');

and want to bring the sums according to this variable .

Script sample,

PolData:

LOAD PolNo,

     [Approval Data] as [APPROVAL DATE],

     UWY,

     Premium AS GWP,

     [Start Date] AS [UPR START DATE],

     [End Date] AS [UPR END DATE]

  

         

FROM

(ooxml, embedded labels, table is Sheet1);

I am using this very simple formula and having null results.

sum({<[APPROVAL DATE]={"<=$(vCalcDate)"}>} GWP)

I have attached a sample data and your helps will be appricated.

1 Solution

Accepted Solutions
MVP
MVP

Re: Filtering with Dates in Set Analysis - Not working

Use variable as below..

SET vCalcDate = Date(Date#('04/30/2016','MM/DD/YYYY'));

Now use below expression

=SUM({<[APPROVAL DATE] = {"<=$(=$(vCalcDate))"}>}GWP)

View solution in original post

6 Replies
sushil353
Honored Contributor II

Re: Filtering with Dates in Set Analysis - Not working

Try this:

sum({<[APPROVAL DATE]={"<=$(=vCalcDate)"}>} GWP)

HTH

egemenekinci
New Contributor III

Re: Filtering with Dates in Set Analysis - Not working

sushil353‌ It is also not working mate..

MVP
MVP

Re: Filtering with Dates in Set Analysis - Not working

Use variable as below..

SET vCalcDate = Date(Date#('04/30/2016','MM/DD/YYYY'));

Now use below expression

=SUM({<[APPROVAL DATE] = {"<=$(=$(vCalcDate))"}>}GWP)

View solution in original post

Highlighted
egemenekinci
New Contributor III

Re: Filtering with Dates in Set Analysis - Not working

mrkachhiaimp‌ Thanks Manish, it is working perfectly.

egemenekinci
New Contributor III

Re: Filtering with Dates in Set Analysis - Not working

Also can you explain why we are using 2 dollar expansion in this formula ? Thanks.

MVP
MVP

Re: Filtering with Dates in Set Analysis - Not working

Because you have used SET variable inside script..

The Magic of Variables